[Home] [Help]
PACKAGE BODY: APPS.PQP_HROSS_INTEGRATION_SWI
Source
1 Package Body PQP_HROSS_Integration_SWI As
2 /* $Header: pqphrossintgswi.pkb 120.0 2005/05/29 02:23:47 appldev noship $ */
3
4 -- =============================================================================
5 -- ~ Package variables
6 -- =============================================================================
7 g_package CONSTANT Varchar2(150) := 'PQP_HROSS_Integration_SWI.';
8 -- =============================================================================
9 -- ~ Get_PK_For_Validation: Check to see if the visa history is already exist or not.
10 -- =============================================================================
11 FUNCTION Get_PK_For_Validation
12 (p_port_of_entry IN Varchar2
13 ,p_cntry_entry_form_num IN VARCHAR2) Return BOOLEAN AS
14
15 TYPE csr_lv_rowid IS REF CURSOR;
16 cur_rowid csr_lv_rowid;
17 SQLstmt varchar2(2000);
18 lv_rowid varchar2(1000);
19 l_proc_name CONSTANT varchar2(150):= g_package ||'Get_PK_For_Validation';
20
21 BEGIN
22
23 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
24
25 SQLstmt:=' SELECT rowid
26 FROM igs_pe_visit_histry
27 WHERE port_of_entry = :1
28 AND cntry_entry_form_num = :2';
29 OPEN cur_rowid FOR SQLstmt
30 Using p_port_of_entry
31 ,p_cntry_entry_form_num;
32 FETCH cur_rowid INTO lv_rowid;
33 IF (cur_rowid%FOUND) THEN
34 CLOSE cur_rowid;
35 RETURN(TRUE);
36 ELSE
37 CLOSE cur_rowid;
38 RETURN(FALSE);
39 END IF;
40
41 Hr_Utility.set_location('Leaving: '||l_proc_name, 50);
42
43 END Get_PK_For_Validation;
44
45 -- =============================================================================
46 -- ~ Create_OSS_Person:
47 -- =============================================================================
48 PROCEDURE Create_OSS_Person
49 (p_business_group_id IN Number
50 ,p_dup_person_id IN Number
51 ,p_effective_date IN Date
52 -- Person Details: Per_All_People_F
53 ,p_party_id IN Number
54 ,p_last_name IN Varchar2
55 ,p_middle_name IN Varchar2
56 ,p_first_name IN Varchar2
57 ,p_suffix IN Varchar2
58 ,p_prefix IN Varchar2
59 ,p_title IN Varchar2
60 ,p_email_address IN Varchar2
61 ,p_preferred_name IN Varchar2
62 ,p_marital_status IN Varchar2
63 ,p_sex IN Varchar2
64 ,p_nationality IN Varchar2
65 ,p_national_identifier IN Varchar2
66 ,p_date_of_birth IN Date
67 ,p_date_of_hire IN Date
68 ,p_employee_number IN Varchar2
69 ,p_person_type_id IN Number
70 ,p_date_employee_data_verified IN Date
71 ,p_expense_check_send_to_addres IN Varchar2
72 ,p_previous_last_name IN Varchar2
73 ,p_registered_disabled_flag IN Varchar2
74 ,p_vendor_id IN Number
75 ,p_date_of_death IN Date
76 ,p_background_check_status IN Varchar2
77 ,p_background_date_check IN Date
78 ,p_blood_type IN Varchar2
79 ,p_correspondence_language IN Varchar2
80 ,p_fast_path_employee IN Varchar2
81 ,p_fte_capacity IN Number
82 ,p_honors IN Varchar2
83 ,p_last_medical_test_by IN Varchar2
84 ,p_last_medical_test_date IN Date
85 ,p_mailstop IN Varchar2
86 ,p_office_number IN Varchar2
87 ,p_on_military_service IN Varchar2
88 ,p_pre_name_adjunct IN Varchar2
89 ,p_projected_start_date IN Date
90 ,p_resume_exists IN Varchar2
91 ,p_resume_last_updated IN Date
92 ,p_second_passport_exists IN Varchar2
93 ,p_student_status IN Varchar2
94 ,p_work_schedule IN Varchar2
95 ,p_benefit_group_id IN Number
96 ,p_receipt_of_death_cert_date IN Date
97 ,p_coord_ben_med_pln_no IN Varchar2
98 ,p_coord_ben_no_cvg_flag IN Varchar2
99 ,p_coord_ben_med_ext_er IN Varchar2
100 ,p_coord_ben_med_pl_name IN Varchar2
101 ,p_coord_ben_med_insr_crr_name IN Varchar2
102 ,p_coord_ben_med_insr_crr_ident IN Varchar2
103 ,p_coord_ben_med_cvg_strt_dt IN Date
104 ,p_coord_ben_med_cvg_end_dt IN Date
105 ,p_uses_tobacco_flag IN Varchar2
106 ,p_dpdnt_adoption_date IN Date
107 ,p_dpdnt_vlntry_svce_flag IN Varchar2
108 ,p_original_date_of_hire IN Date
109 ,p_adjusted_svc_date IN Date
110 ,p_town_of_birth IN Varchar2
111 ,p_region_of_birth IN Varchar2
112 ,p_country_of_birth IN Varchar2
113 ,p_global_person_id IN Varchar2
114 -- Person DF
115 ,p_per_attribute_category IN Varchar2
116 ,p_per_attribute1 IN Varchar2
117 ,p_per_attribute2 IN Varchar2
118 ,p_per_attribute3 IN Varchar2
119 ,p_per_attribute4 IN Varchar2
120 ,p_per_attribute5 IN Varchar2
121 ,p_per_attribute6 IN Varchar2
122 ,p_per_attribute7 IN Varchar2
123 ,p_per_attribute8 IN Varchar2
124 ,p_per_attribute9 IN Varchar2
125 ,p_per_attribute10 IN Varchar2
126 ,p_per_attribute11 IN Varchar2
127 ,p_per_attribute12 IN Varchar2
128 ,p_per_attribute13 IN Varchar2
129 ,p_per_attribute14 IN Varchar2
130 ,p_per_attribute15 IN Varchar2
131 ,p_per_attribute16 IN Varchar2
132 ,p_per_attribute17 IN Varchar2
133 ,p_per_attribute18 IN Varchar2
134 ,p_per_attribute19 IN Varchar2
135 ,p_per_attribute20 IN Varchar2
136 ,p_per_attribute21 IN Varchar2
137 ,p_per_attribute22 IN Varchar2
138 ,p_per_attribute23 IN Varchar2
139 ,p_per_attribute24 IN Varchar2
140 ,p_per_attribute25 IN Varchar2
141 ,p_per_attribute26 IN Varchar2
142 ,p_per_attribute27 IN Varchar2
143 ,p_per_attribute28 IN Varchar2
144 ,p_per_attribute29 IN Varchar2
145 ,p_per_attribute30 IN Varchar2
146 -- Person DDF
147 ,p_per_information_category IN Varchar2
148 ,p_per_information1 IN Varchar2
149 ,p_per_information2 IN Varchar2
150 ,p_per_information3 IN Varchar2
151 ,p_per_information4 IN Varchar2
152 ,p_per_information5 IN Varchar2
153 ,p_per_information6 IN Varchar2
154 ,p_per_information7 IN Varchar2
155 ,p_per_information8 IN Varchar2
156 ,p_per_information9 IN Varchar2
157 ,p_per_information10 IN Varchar2
158 ,p_per_information11 IN Varchar2
159 ,p_per_information12 IN Varchar2
160 ,p_per_information13 IN Varchar2
161 ,p_per_information14 IN Varchar2
162 ,p_per_information15 IN Varchar2
163 ,p_per_information16 IN Varchar2
164 ,p_per_information17 IN Varchar2
165 ,p_per_information18 IN Varchar2
166 ,p_per_information19 IN Varchar2
167 ,p_per_information20 IN Varchar2
168 ,p_per_information21 IN Varchar2
169 ,p_per_information22 IN Varchar2
170 ,p_per_information23 IN Varchar2
171 ,p_per_information24 IN Varchar2
172 ,p_per_information25 IN Varchar2
173 ,p_per_information26 IN Varchar2
174 ,p_per_information27 IN Varchar2
175 ,p_per_information28 IN Varchar2
176 ,p_per_information29 IN Varchar2
177 ,p_per_information30 IN Varchar2
178 -- Primary Address: Per_Addresses
179 ,p_pradd_ovlapval_override IN Varchar2
180 ,p_address_type IN Varchar2
181 ,p_adr_comments IN Varchar2
182 ,p_primary_flag IN Varchar2
183 ,p_address_style IN Varchar2
184 ,p_address_line1 IN Varchar2
185 ,p_address_line2 IN Varchar2
186 ,p_address_line3 IN Varchar2
187 ,p_region1 IN Varchar2
188 ,p_region2 IN Varchar2
189 ,p_region3 IN Varchar2
190 ,p_town_or_city IN Varchar2
191 ,p_country IN Varchar2
192 ,p_postal_code IN Varchar2
193 ,p_telephone_no1 IN Varchar2
194 ,p_telephone_no2 IN Varchar2
195 ,p_telephone_no3 IN Varchar2
196 ,p_address_date_from IN Date
197 ,p_address_date_to IN Date
198 ,p_adr_attribute_category IN Varchar2
199 ,p_adr_attribute1 IN Varchar2
200 ,p_adr_attribute2 IN Varchar2
201 ,p_adr_attribute3 IN Varchar2
202 ,p_adr_attribute4 IN Varchar2
203 ,p_adr_attribute5 IN Varchar2
204 ,p_adr_attribute6 IN Varchar2
205 ,p_adr_attribute7 IN Varchar2
206 ,p_adr_attribute8 IN Varchar2
207 ,p_adr_attribute9 IN Varchar2
208 ,p_adr_attribute10 IN Varchar2
209 ,p_adr_attribute11 IN Varchar2
210 ,p_adr_attribute12 IN Varchar2
211 ,p_adr_attribute13 IN Varchar2
212 ,p_adr_attribute14 IN Varchar2
213 ,p_adr_attribute15 IN Varchar2
214 ,p_adr_attribute16 IN Varchar2
215 ,p_adr_attribute17 IN Varchar2
216 ,p_adr_attribute18 IN Varchar2
217 ,p_adr_attribute19 IN Varchar2
218 ,p_adr_attribute20 IN Varchar2
219 ,p_add_information13 IN Varchar2
220 ,p_add_information14 IN Varchar2
221 ,p_add_information15 IN Varchar2
222 ,p_add_information16 IN Varchar2
223 ,p_add_information17 IN Varchar2
224 ,p_add_information18 IN Varchar2
225 ,p_add_information19 IN Varchar2
226 ,p_add_information20 IN Varchar2
227 -- Person Phones: Per_Phones
228 ,p_phone_type IN Varchar2
229 ,p_phone_number IN Varchar2
230 ,p_phone_date_from IN Date
231 ,p_phone_date_to IN Date
232 -- Person Contact: Per_Contact_Relationships
233 ,p_contact_type IN Varchar2
234 ,p_contact_name IN Varchar2
235 ,p_primary_contact IN Varchar2
236 ,p_primary_relationship IN Varchar2
237 ,p_contact_date_from IN Date
238 ,p_contact_date_to IN Date
239 ,p_return_status OUT NOCOPY Varchar2
240 ,p_dup_asg_id IN Number
241 ,p_mode_type IN Varchar2
242 ) AS
243 --
244 -- Variables for IN/OUT parameters
245 --
246 l_proc CONSTANT Varchar2(150) := g_package ||'Create_OSS_Person';
247 BEGIN
248 Hr_Utility.set_location(' Entering:' || l_proc,5);
249 --
250 -- Issue a savepoint
251 --
252 SAVEPOINT Create_OSS_Person_swi;
253 --
254 -- Initialise Multiple Message Detection
255 --
256 Hr_Multi_Message.enable_message_list;
257 --
258 -- Call API
259 --
260 Pqp_Hross_Integration.Create_OSS_Person
261 (p_business_group_id => p_business_group_id
262 ,p_dup_person_id => p_dup_person_id
263 ,p_effective_date => p_effective_date
264 -- Person Details: Per_All_People_F
265 ,p_party_id => p_party_id
266 ,p_last_name => p_last_name
267 ,p_middle_name => p_middle_name
268 ,p_first_name => p_first_name
269 ,p_suffix => p_suffix
270 ,p_prefix => p_prefix
271 ,p_title => p_title
272 ,p_email_address => p_email_address
273 ,p_preferred_name => p_preferred_name
274 ,p_marital_status => p_marital_status
275 ,p_sex => p_sex
276 ,p_nationality => p_nationality
277 ,p_national_identifier => p_national_identifier
278 ,p_date_of_birth => p_date_of_birth
279 ,p_date_of_hire => p_date_of_hire
280 ,p_employee_number => p_employee_number
281 ,p_person_type_id => p_person_type_id
282 ,p_date_employee_data_verified => p_date_employee_data_verified
283 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
284 ,p_previous_last_name => p_previous_last_name
285 ,p_registered_disabled_flag => p_registered_disabled_flag
286 ,p_vendor_id => p_vendor_id
287 ,p_date_of_death => p_date_of_death
288 ,p_background_check_status => p_background_check_status
289 ,p_background_date_check => p_background_date_check
290 ,p_blood_type => p_blood_type
291 ,p_correspondence_language => p_correspondence_language
292 ,p_fast_path_employee => p_fast_path_employee
293 ,p_fte_capacity => p_fte_capacity
294 ,p_honors => p_honors
295 ,p_last_medical_test_by => p_last_medical_test_by
296 ,p_last_medical_test_date => p_last_medical_test_date
297 ,p_mailstop => p_mailstop
298 ,p_office_number => p_office_number
299 ,p_on_military_service => p_on_military_service
300 ,p_pre_name_adjunct => p_pre_name_adjunct
301 ,p_projected_start_date => p_projected_start_date
302 ,p_resume_exists => p_resume_exists
303 ,p_resume_last_updated => p_resume_last_updated
304 ,p_second_passport_exists => p_second_passport_exists
305 ,p_student_status => p_student_status
306 ,p_work_schedule => p_work_schedule
307 ,p_benefit_group_id => p_benefit_group_id
308 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
309 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
310 ,p_coord_ben_no_cvg_flag => p_coord_ben_no_cvg_flag
311 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
312 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
313 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
314 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
315 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
316 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
317 ,p_uses_tobacco_flag => p_uses_tobacco_flag
318 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
319 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
320 ,p_original_date_of_hire => p_original_date_of_hire
321 ,p_adjusted_svc_date => p_adjusted_svc_date
322 ,p_town_of_birth => p_town_of_birth
323 ,p_region_of_birth => p_region_of_birth
324 ,p_country_of_birth => p_country_of_birth
325 ,p_global_person_id => p_global_person_id
326 -- Person DF
327 ,p_per_attribute_category => p_per_attribute_category
328 ,p_per_attribute1 => p_per_attribute1
329 ,p_per_attribute2 => p_per_attribute2
330 ,p_per_attribute3 => p_per_attribute3
331 ,p_per_attribute4 => p_per_attribute4
332 ,p_per_attribute5 => p_per_attribute5
333 ,p_per_attribute6 => p_per_attribute6
334 ,p_per_attribute7 => p_per_attribute7
335 ,p_per_attribute8 => p_per_attribute8
336 ,p_per_attribute9 => p_per_attribute9
337 ,p_per_attribute10 => p_per_attribute10
338 ,p_per_attribute11 => p_per_attribute11
339 ,p_per_attribute12 => p_per_attribute12
340 ,p_per_attribute13 => p_per_attribute13
341 ,p_per_attribute14 => p_per_attribute14
342 ,p_per_attribute15 => p_per_attribute15
343 ,p_per_attribute16 => p_per_attribute16
344 ,p_per_attribute17 => p_per_attribute17
345 ,p_per_attribute18 => p_per_attribute18
346 ,p_per_attribute19 => p_per_attribute19
347 ,p_per_attribute20 => p_per_attribute20
348 ,p_per_attribute21 => p_per_attribute21
349 ,p_per_attribute22 => p_per_attribute22
350 ,p_per_attribute23 => p_per_attribute23
351 ,p_per_attribute24 => p_per_attribute24
352 ,p_per_attribute25 => p_per_attribute25
353 ,p_per_attribute26 => p_per_attribute26
354 ,p_per_attribute27 => p_per_attribute27
355 ,p_per_attribute28 => p_per_attribute28
356 ,p_per_attribute29 => p_per_attribute29
357 ,p_per_attribute30 => p_per_attribute30
358 -- Person DDF
359 ,p_per_information_category => p_per_information_category
360 ,p_per_information1 => p_per_information1
361 ,p_per_information2 => p_per_information2
362 ,p_per_information3 => p_per_information3
363 ,p_per_information4 => p_per_information4
364 ,p_per_information5 => p_per_information5
365 ,p_per_information6 => p_per_information6
366 ,p_per_information7 => p_per_information7
367 ,p_per_information8 => p_per_information8
368 ,p_per_information9 => p_per_information9
369 ,p_per_information10 => p_per_information10
370 ,p_per_information11 => p_per_information11
371 ,p_per_information12 => p_per_information12
372 ,p_per_information13 => p_per_information13
373 ,p_per_information14 => p_per_information14
374 ,p_per_information15 => p_per_information15
375 ,p_per_information16 => p_per_information16
376 ,p_per_information17 => p_per_information17
377 ,p_per_information18 => p_per_information18
378 ,p_per_information19 => p_per_information19
379 ,p_per_information20 => p_per_information20
380 ,p_per_information21 => p_per_information21
381 ,p_per_information22 => p_per_information22
382 ,p_per_information23 => p_per_information23
383 ,p_per_information24 => p_per_information24
384 ,p_per_information25 => p_per_information25
385 ,p_per_information26 => p_per_information26
386 ,p_per_information27 => p_per_information27
387 ,p_per_information28 => p_per_information28
388 ,p_per_information29 => p_per_information29
389 ,p_per_information30 => p_per_information30
390 -- Primary Address: Per_Addresses
391 ,p_pradd_ovlapval_override => p_pradd_ovlapval_override
392 ,p_address_type => p_address_type
393 ,p_adr_comments => p_adr_comments
394 ,p_primary_flag => p_primary_flag
395 ,p_address_style => p_address_style
396 ,p_address_line1 => p_address_line1
397 ,p_address_line2 => p_address_line2
398 ,p_address_line3 => p_address_line3
399 ,p_region1 => p_region1
400 ,p_region2 => p_region2
401 ,p_region3 => p_region3
402 ,p_town_or_city => p_town_or_city
403 ,p_country => p_country
404 ,p_postal_code => p_postal_code
405 ,p_telephone_no1 => p_telephone_no1
406 ,p_telephone_no2 => p_telephone_no2
407 ,p_telephone_no3 => p_telephone_no3
408 ,p_address_date_from => p_address_date_from
409 ,p_address_date_to => p_address_date_to
410 ,p_adr_attribute_category => p_adr_attribute_category
411 ,p_adr_attribute1 => p_adr_attribute1
412 ,p_adr_attribute2 => p_adr_attribute2
413 ,p_adr_attribute3 => p_adr_attribute3
414 ,p_adr_attribute4 => p_adr_attribute4
415 ,p_adr_attribute5 => p_adr_attribute5
416 ,p_adr_attribute6 => p_adr_attribute6
417 ,p_adr_attribute7 => p_adr_attribute7
418 ,p_adr_attribute8 => p_adr_attribute8
419 ,p_adr_attribute9 => p_adr_attribute9
420 ,p_adr_attribute10 => p_adr_attribute10
421 ,p_adr_attribute11 => p_adr_attribute11
422 ,p_adr_attribute12 => p_adr_attribute12
423 ,p_adr_attribute13 => p_adr_attribute13
424 ,p_adr_attribute14 => p_adr_attribute14
425 ,p_adr_attribute15 => p_adr_attribute15
426 ,p_adr_attribute16 => p_adr_attribute16
427 ,p_adr_attribute17 => p_adr_attribute17
428 ,p_adr_attribute18 => p_adr_attribute18
429 ,p_adr_attribute19 => p_adr_attribute19
430 ,p_adr_attribute20 => p_adr_attribute20
431 ,p_add_information13 => p_add_information13
432 ,p_add_information14 => p_add_information14
433 ,p_add_information15 => p_add_information15
434 ,p_add_information16 => p_add_information16
435 ,p_add_information17 => p_add_information17
436 ,p_add_information18 => p_add_information18
437 ,p_add_information19 => p_add_information19
438 ,p_add_information20 => p_add_information20
439 -- Person Phones: Per_Phones
440 ,p_phone_type => p_phone_type
441 ,p_phone_number => p_phone_number
442 ,p_phone_date_from => p_phone_date_from
443 ,p_phone_date_to => p_phone_date_to
444 -- Person Contact: Per_Contact_Relationships
445 ,p_contact_type => p_contact_type
446 ,p_contact_name => p_contact_name
447 ,p_primary_contact => p_primary_contact
448 ,p_primary_relationship => p_primary_relationship
449 ,p_contact_date_from => p_contact_date_from
450 ,p_contact_date_to => p_contact_date_to
451 ,p_return_status => p_return_status
452 ,p_dup_asg_id => p_dup_asg_id
453 ,p_mode_type => p_mode_type
454 );
455 --
456 -- Convert API warning boolean parameter values to specific
457 -- messages and add them to Multiple Message List
458 --
459 --
460 -- Convert API non-warning boolean parameter values
461 --
462 --
463 -- Derive the API return status value based on whether
464 -- messages of any type exist in the Multiple Message List.
465 -- Also disable Multiple Message Detection.
466 --
467 p_return_status := Hr_Multi_Message.get_return_status_disable;
468 Hr_Utility.set_location(' Leaving:' || l_proc,50);
469 --
470 EXCEPTION
471 WHEN Hr_Multi_Message.error_message_exist THEN
472 --
473 -- Catch the Multiple Message List exception which
474 -- indicates API processing has been aborted because
475 -- at least one message exists in the list.
476 --
477 ROLLBACK TO Create_OSS_Person_swi;
478 --
479 -- Reset IN OUT parameters and set OUT parameters
480 --
481 p_return_status := Hr_Multi_Message.get_return_status_disable;
482 Hr_Utility.set_location(' Leaving:' || l_proc, 70);
483 WHEN Others THEN
484 --
485 -- When Multiple Message Detection is enabled catch
486 -- any Application specific or other unexpected
487 -- exceptions. Adding appropriate details to the
488 -- Multiple Message List. Otherwise re-raise the
489 -- error.
490 --
491 ROLLBACK TO Create_OSS_Person_swi;
492 IF Hr_Multi_Message.unexpected_error_add(l_proc) THEN
493 Hr_Utility.set_location(' Leaving:' || l_proc,80);
494
495 END IF;
496 --
497 -- Reset IN OUT and set OUT parameters
498 --
499 p_return_status := Hr_Multi_Message.get_return_status_disable;
500 Hr_Utility.set_location(' Leaving:' || l_proc,90);
501
502 END Create_OSS_Person;
503 -- =============================================================================
504 -- ~ Upd_OSS_Person_Asg:
505 -- =============================================================================
506 PROCEDURE Upd_OSS_Person_Asg
507 (p_effective_date IN Date
508 ,p_datetrack_update_mode IN Varchar2
509 ,p_assignment_id IN Number
510 ,p_party_id IN Number
511 ,p_business_group_id IN Number
512 ,p_valiDate IN Boolean
513 ,p_called_from_mass_upDate IN Boolean
514 --
515 ,p_grade_id IN Number
516 ,p_position_id IN Number
517 ,p_job_id IN Number
518 ,p_payroll_id IN Number
519 ,p_location_id IN Number
520 ,p_organization_id IN Number
521 ,p_pay_basis_id IN Number
522 ,p_employment_category IN Varchar2
523 ,p_assignment_category IN Varchar2
524 --
525 ,p_supervisor_id IN Number
526 ,p_assignment_number IN Varchar2
527 ,p_change_reason IN Varchar2
528 ,p_assignment_status_type_id IN Number
529 ,p_comments IN Varchar2
530 ,p_Date_probation_end IN Date
531 ,p_default_code_comb_id IN Number
532 ,p_frequency IN Varchar2
533 ,p_internal_address_line IN Varchar2
534 ,p_manager_flag IN Varchar2
535 ,p_normal_hours IN Number
536 ,p_perf_review_period IN Number
537 ,p_perf_review_period_frequency IN Varchar2
538 ,p_probation_period IN Number
539 ,p_probation_unit IN Varchar2
540 ,p_sal_review_period IN Number
541 ,p_sal_review_period_frequency IN Varchar2
542 ,p_set_of_books_id IN Number
543 ,p_source_type IN Varchar2
544 ,p_time_normal_finish IN Varchar2
545 ,p_time_normal_start IN Varchar2
546 ,p_bargaining_unit_code IN Varchar2
547 ,p_labour_union_member_flag IN Varchar2
548 ,p_hourly_salaried_code IN Varchar2
549 ,p_title IN Varchar2
550 ,p_notice_period IN Number
551 ,p_notice_period_uom IN Varchar2
552 ,p_employee_category IN Varchar2
553 ,p_work_at_home IN Varchar2
554 ,p_job_post_source_name IN Varchar2
555 ,p_supervisor_assignment_id IN Number
556 --People Group Keyflex Field
557 ,p_people_group_id IN Number
558 ,p_pgrp_segment1 IN Varchar2
559 ,p_pgrp_segment2 IN Varchar2
560 ,p_pgrp_segment3 IN Varchar2
561 ,p_pgrp_segment4 IN Varchar2
562 ,p_pgrp_segment5 IN Varchar2
563 ,p_pgrp_segment6 IN Varchar2
564 ,p_pgrp_segment7 IN Varchar2
565 ,p_pgrp_segment8 IN Varchar2
566 ,p_pgrp_segment9 IN Varchar2
567 ,p_pgrp_segment10 IN Varchar2
568 ,p_pgrp_segment11 IN Varchar2
569 ,p_pgrp_segment12 IN Varchar2
570 ,p_pgrp_segment13 IN Varchar2
571 ,p_pgrp_segment14 IN Varchar2
572 ,p_pgrp_segment15 IN Varchar2
573 ,p_pgrp_segment16 IN Varchar2
574 ,p_pgrp_segment17 IN Varchar2
575 ,p_pgrp_segment18 IN Varchar2
576 ,p_pgrp_segment19 IN Varchar2
577 ,p_pgrp_segment20 IN Varchar2
578 ,p_pgrp_segment21 IN Varchar2
579 ,p_pgrp_segment22 IN Varchar2
580 ,p_pgrp_segment23 IN Varchar2
581 ,p_pgrp_segment24 IN Varchar2
582 ,p_pgrp_segment25 IN Varchar2
583 ,p_pgrp_segment26 IN Varchar2
584 ,p_pgrp_segment27 IN Varchar2
585 ,p_pgrp_segment28 IN Varchar2
586 ,p_pgrp_segment29 IN Varchar2
587 ,p_pgrp_segment30 IN Varchar2
588 ,p_pgrp_concat_segments IN Varchar2
589 --Soft Coding KeyflexId
590 ,p_soft_coding_keyflex_id IN Number
591 ,p_soft_concat_segments IN Varchar2
592 ,p_scl_segment1 IN Varchar2
593 ,p_scl_segment2 IN Varchar2
594 ,p_scl_segment3 IN Varchar2
595 ,p_scl_segment4 IN Varchar2
596 ,p_scl_segment5 IN Varchar2
597 ,p_scl_segment6 IN Varchar2
598 ,p_scl_segment7 IN Varchar2
599 ,p_scl_segment8 IN Varchar2
600 ,p_scl_segment9 IN Varchar2
601 ,p_scl_segment10 IN Varchar2
602 ,p_scl_segment11 IN Varchar2
603 ,p_scl_segment12 IN Varchar2
604 ,p_scl_segment13 IN Varchar2
605 ,p_scl_segment14 IN Varchar2
606 ,p_scl_segment15 IN Varchar2
607 ,p_scl_segment16 IN Varchar2
608 ,p_scl_segment17 IN Varchar2
609 ,p_scl_segment18 IN Varchar2
610 ,p_scl_segment19 IN Varchar2
611 ,p_scl_segment20 IN Varchar2
612 ,p_scl_segment21 IN Varchar2
613 ,p_scl_segment22 IN Varchar2
614 ,p_scl_segment23 IN Varchar2
615 ,p_scl_segment24 IN Varchar2
616 ,p_scl_segment25 IN Varchar2
617 ,p_scl_segment26 IN Varchar2
618 ,p_scl_segment27 IN Varchar2
619 ,p_scl_segment28 IN Varchar2
620 ,p_scl_segment29 IN Varchar2
621 ,p_scl_segment30 IN Varchar2
622 -- Assignment DF Information
623 ,p_ass_attribute_category IN Varchar2
624 ,p_ass_attribute1 IN Varchar2
625 ,p_ass_attribute2 IN Varchar2
626 ,p_ass_attribute3 IN Varchar2
627 ,p_ass_attribute4 IN Varchar2
628 ,p_ass_attribute5 IN Varchar2
629 ,p_ass_attribute6 IN Varchar2
630 ,p_ass_attribute7 IN Varchar2
631 ,p_ass_attribute8 IN Varchar2
632 ,p_ass_attribute9 IN Varchar2
633 ,p_ass_attribute10 IN Varchar2
634 ,p_ass_attribute11 IN Varchar2
635 ,p_ass_attribute12 IN Varchar2
636 ,p_ass_attribute13 IN Varchar2
637 ,p_ass_attribute14 IN Varchar2
638 ,p_ass_attribute15 IN Varchar2
639 ,p_ass_attribute16 IN Varchar2
640 ,p_ass_attribute17 IN Varchar2
641 ,p_ass_attribute18 IN Varchar2
642 ,p_ass_attribute19 IN Varchar2
643 ,p_ass_attribute20 IN Varchar2
644 ,p_ass_attribute21 IN Varchar2
645 ,p_ass_attribute22 IN Varchar2
646 ,p_ass_attribute23 IN Varchar2
647 ,p_ass_attribute24 IN Varchar2
648 ,p_ass_attribute25 IN Varchar2
649 ,p_ass_attribute26 IN Varchar2
650 ,p_ass_attribute27 IN Varchar2
651 ,p_ass_attribute28 IN Varchar2
652 ,p_ass_attribute29 IN Varchar2
653 ,p_ass_attribute30 IN Varchar2
654 --
655 ,p_grade_ladder_pgm_id IN Number
656 ,p_special_ceiling_step_id IN Number
657 ,p_cagr_grade_def_id IN Number
658 ,p_contract_id IN Number
659 ,p_establishment_id IN Number
660 ,p_collective_agreement_id IN Number
661 ,p_cagr_id_flex_num IN Number
662 ,p_cag_segment1 IN Varchar2
663 ,p_cag_segment2 IN Varchar2
664 ,p_cag_segment3 IN Varchar2
665 ,p_cag_segment4 IN Varchar2
666 ,p_cag_segment5 IN Varchar2
667 ,p_cag_segment6 IN Varchar2
668 ,p_cag_segment7 IN Varchar2
669 ,p_cag_segment8 IN Varchar2
670 ,p_cag_segment9 IN Varchar2
671 ,p_cag_segment10 IN Varchar2
672 ,p_cag_segment11 IN Varchar2
673 ,p_cag_segment12 IN Varchar2
674 ,p_cag_segment13 IN Varchar2
675 ,p_cag_segment14 IN Varchar2
676 ,p_cag_segment15 IN Varchar2
677 ,p_cag_segment16 IN Varchar2
678 ,p_cag_segment17 IN Varchar2
679 ,p_cag_segment18 IN Varchar2
680 ,p_cag_segment19 IN Varchar2
681 ,p_cag_segment20 IN Varchar2
682 ,p_return_status OUT NOCOPY Varchar2
683 ,p_FICA_exempt IN Varchar2
684 ) AS
685 --
686 -- Variables for IN/OUT parameters
687 --
688 l_proc CONSTANT Varchar2(150) := g_package ||'Upd_OSS_Person_Asg';
689 l_FICA_exempt Varchar2(15);
690 BEGIN
691 Hr_Utility.set_location(' Entering:' || l_proc,5);
692 --
693 -- Issue a savepoint
694 --
695 SAVEPOINT Upd_OSS_Person_Asg_SWI;
696 IF p_FICA_exempt Is NULL THEN
697 l_FICA_exempt := 'N';
698 ELSE
699 l_FICA_exempt := p_FICA_exempt;
700 END IF;
701 --
702 -- Initialise Multiple Message Detection
703 --
704 Hr_Multi_Message.enable_message_list;
705 --
706 -- Call API
707 --
708 Pqp_Hross_Integration.Upd_OSS_Person_Asg
709 (p_effective_date => p_effective_date
710 ,p_datetrack_update_mode => p_datetrack_update_mode
711 ,p_assignment_id => p_assignment_id
712 ,p_party_id => p_party_id
713 ,p_business_group_id => p_business_group_id
714 ,p_valiDate => FALSE
715 ,p_called_from_mass_upDate => FALSE
716 --
717 ,p_grade_id => p_grade_id
718 ,p_position_id => p_position_id
719 ,p_job_id => p_job_id
720 ,p_payroll_id => p_payroll_id
721 ,p_location_id => p_location_id
722 ,p_organization_id => p_organization_id
723 ,p_pay_basis_id => p_pay_basis_id
724 ,p_employment_category => p_employment_category
725 ,p_assignment_category => p_assignment_category
726 --
727 ,p_supervisor_id => p_supervisor_id
728 ,p_assignment_number => p_assignment_number
729 ,p_change_reason => p_change_reason
730 ,p_assignment_status_type_id => p_assignment_status_type_id
731 ,p_comments => p_comments
732 ,p_date_probation_end => p_date_probation_end
733 ,p_default_code_comb_id => p_default_code_comb_id
734 ,p_frequency => p_frequency
735 ,p_internal_address_line => p_internal_address_line
736 ,p_manager_flag => p_manager_flag
737 ,p_normal_hours => p_normal_hours
738 ,p_perf_review_period => p_perf_review_period
739 ,p_perf_review_period_frequency => p_perf_review_period_frequency
740 ,p_probation_period => p_probation_period
741 ,p_probation_unit => p_probation_unit
742 ,p_sal_review_period => p_sal_review_period
743 ,p_sal_review_period_frequency => p_sal_review_period_frequency
744 ,p_set_of_books_id => p_set_of_books_id
745 ,p_source_type => p_source_type
746 ,p_time_normal_finish => p_time_normal_finish
747 ,p_time_normal_start => p_time_normal_start
748 ,p_bargaining_unit_code => p_bargaining_unit_code
749 ,p_labour_union_member_flag => p_labour_union_member_flag
750 ,p_hourly_salaried_code => p_hourly_salaried_code
751 ,p_title => p_title
752 ,p_notice_period => p_notice_period
753 ,p_notice_period_uom => p_notice_period_uom
754 ,p_employee_category => p_employee_category
755 ,p_work_at_home => p_work_at_home
756 ,p_job_post_source_name => p_job_post_source_name
757 ,p_supervisor_assignment_id => p_supervisor_assignment_id
758 --People Group Keyflex Field
759 ,p_people_group_id => p_people_group_id
760 ,p_pgrp_segment1 => p_pgrp_segment1
761 ,p_pgrp_segment2 => p_pgrp_segment2
762 ,p_pgrp_segment3 => p_pgrp_segment3
763 ,p_pgrp_segment4 => p_pgrp_segment4
764 ,p_pgrp_segment5 => p_pgrp_segment5
765 ,p_pgrp_segment6 => p_pgrp_segment6
766 ,p_pgrp_segment7 => p_pgrp_segment7
767 ,p_pgrp_segment8 => p_pgrp_segment8
768 ,p_pgrp_segment9 => p_pgrp_segment9
769 ,p_pgrp_segment10 => p_pgrp_segment10
770 ,p_pgrp_segment11 => p_pgrp_segment11
771 ,p_pgrp_segment12 => p_pgrp_segment12
772 ,p_pgrp_segment13 => p_pgrp_segment13
773 ,p_pgrp_segment14 => p_pgrp_segment14
774 ,p_pgrp_segment15 => p_pgrp_segment15
775 ,p_pgrp_segment16 => p_pgrp_segment16
776 ,p_pgrp_segment17 => p_pgrp_segment17
777 ,p_pgrp_segment18 => p_pgrp_segment18
778 ,p_pgrp_segment19 => p_pgrp_segment19
779 ,p_pgrp_segment20 => p_pgrp_segment20
780 ,p_pgrp_segment21 => p_pgrp_segment21
781 ,p_pgrp_segment22 => p_pgrp_segment22
782 ,p_pgrp_segment23 => p_pgrp_segment23
783 ,p_pgrp_segment24 => p_pgrp_segment24
784 ,p_pgrp_segment25 => p_pgrp_segment25
785 ,p_pgrp_segment26 => p_pgrp_segment26
786 ,p_pgrp_segment27 => p_pgrp_segment27
787 ,p_pgrp_segment28 => p_pgrp_segment28
788 ,p_pgrp_segment29 => p_pgrp_segment29
789 ,p_pgrp_segment30 => p_pgrp_segment30
790 ,p_pgrp_concat_segments => p_pgrp_concat_segments
791 -- Soft Coding KeyflexId
792 ,p_soft_coding_keyflex_id => p_soft_coding_keyflex_id
793 ,p_soft_concat_segments => p_soft_concat_segments
794 ,p_scl_segment1 => p_scl_segment1
795 ,p_scl_segment2 => p_scl_segment2
796 ,p_scl_segment3 => p_scl_segment3
797 ,p_scl_segment4 => p_scl_segment4
798 ,p_scl_segment5 => p_scl_segment5
799 ,p_scl_segment6 => p_scl_segment6
800 ,p_scl_segment7 => p_scl_segment7
801 ,p_scl_segment8 => p_scl_segment8
802 ,p_scl_segment9 => p_scl_segment9
803 ,p_scl_segment10 => p_scl_segment10
804 ,p_scl_segment11 => p_scl_segment11
805 ,p_scl_segment12 => p_scl_segment12
806 ,p_scl_segment13 => p_scl_segment13
807 ,p_scl_segment14 => p_scl_segment14
808 ,p_scl_segment15 => p_scl_segment15
809 ,p_scl_segment16 => p_scl_segment16
810 ,p_scl_segment17 => p_scl_segment17
811 ,p_scl_segment18 => p_scl_segment18
812 ,p_scl_segment19 => p_scl_segment19
813 ,p_scl_segment20 => p_scl_segment20
814 ,p_scl_segment21 => p_scl_segment21
815 ,p_scl_segment22 => p_scl_segment22
816 ,p_scl_segment23 => p_scl_segment23
817 ,p_scl_segment24 => p_scl_segment24
818 ,p_scl_segment25 => p_scl_segment25
819 ,p_scl_segment26 => p_scl_segment26
820 ,p_scl_segment27 => p_scl_segment27
821 ,p_scl_segment28 => p_scl_segment28
822 ,p_scl_segment29 => p_scl_segment29
823 ,p_scl_segment30 => p_scl_segment30
824 -- Assignment DF Information
825 ,p_ass_attribute_category => p_ass_attribute_category
826 ,p_ass_attribute1 => p_ass_attribute1
827 ,p_ass_attribute2 => p_ass_attribute2
828 ,p_ass_attribute3 => p_ass_attribute3
829 ,p_ass_attribute4 => p_ass_attribute4
830 ,p_ass_attribute5 => p_ass_attribute5
831 ,p_ass_attribute6 => p_ass_attribute6
832 ,p_ass_attribute7 => p_ass_attribute7
833 ,p_ass_attribute8 => p_ass_attribute8
834 ,p_ass_attribute9 => p_ass_attribute9
835 ,p_ass_attribute10 => p_ass_attribute10
836 ,p_ass_attribute11 => p_ass_attribute11
837 ,p_ass_attribute12 => p_ass_attribute12
838 ,p_ass_attribute13 => p_ass_attribute13
839 ,p_ass_attribute14 => p_ass_attribute14
840 ,p_ass_attribute15 => p_ass_attribute15
841 ,p_ass_attribute16 => p_ass_attribute16
842 ,p_ass_attribute17 => p_ass_attribute17
843 ,p_ass_attribute18 => p_ass_attribute18
844 ,p_ass_attribute19 => p_ass_attribute19
845 ,p_ass_attribute20 => p_ass_attribute20
846 ,p_ass_attribute21 => p_ass_attribute21
847 ,p_ass_attribute22 => p_ass_attribute22
848 ,p_ass_attribute23 => p_ass_attribute23
849 ,p_ass_attribute24 => p_ass_attribute24
850 ,p_ass_attribute25 => p_ass_attribute25
851 ,p_ass_attribute26 => p_ass_attribute26
852 ,p_ass_attribute27 => p_ass_attribute27
853 ,p_ass_attribute28 => p_ass_attribute28
854 ,p_ass_attribute29 => p_ass_attribute29
855 ,p_ass_attribute30 => p_ass_attribute30
856 --
857 ,p_grade_ladder_pgm_id => p_grade_ladder_pgm_id
858 ,p_special_ceiling_step_id => p_special_ceiling_step_id
859 ,p_cagr_grade_def_id => p_cagr_grade_def_id
860 ,p_contract_id => p_contract_id
861 ,p_establishment_id => p_establishment_id
862 ,p_collective_agreement_id => p_collective_agreement_id
863 ,p_cagr_id_flex_num => p_cagr_id_flex_num
864 ,p_cag_segment1 => p_cag_segment1
865 ,p_cag_segment2 => p_cag_segment2
866 ,p_cag_segment3 => p_cag_segment3
867 ,p_cag_segment4 => p_cag_segment4
868 ,p_cag_segment5 => p_cag_segment5
869 ,p_cag_segment6 => p_cag_segment6
870 ,p_cag_segment7 => p_cag_segment7
871 ,p_cag_segment8 => p_cag_segment8
872 ,p_cag_segment9 => p_cag_segment9
873 ,p_cag_segment10 => p_cag_segment10
874 ,p_cag_segment11 => p_cag_segment11
875 ,p_cag_segment12 => p_cag_segment12
876 ,p_cag_segment13 => p_cag_segment13
877 ,p_cag_segment14 => p_cag_segment14
878 ,p_cag_segment15 => p_cag_segment15
879 ,p_cag_segment16 => p_cag_segment16
880 ,p_cag_segment17 => p_cag_segment17
881 ,p_cag_segment18 => p_cag_segment18
882 ,p_cag_segment19 => p_cag_segment19
883 ,p_cag_segment20 => p_cag_segment20
884 ,p_return_status => p_return_status
885 ,p_FICA_exempt => l_FICA_exempt
886 );
887 --
888 -- Convert API warning boolean parameter values to specific
889 -- messages and add them to Multiple Message List
890 --
891 --
892 -- Convert API non-warning boolean parameter values
893 --
894 --
895 -- Derive the API return status value based on whether
896 -- messages of any type exist in the Multiple Message List.
897 -- Also disable Multiple Message Detection.
898 --
899 p_return_status := Hr_Multi_Message.get_return_status_disable;
900 Hr_Utility.set_location(' Leaving:' || l_proc,50);
901 --
902 EXCEPTION
903 WHEN Hr_Multi_Message.error_message_exist THEN
904 --
905 -- Catch the Multiple Message List exception which
906 -- indicates API processing has been aborted because
907 -- at least one message exists in the list.
908 --
909 ROLLBACK TO Upd_OSS_Person_Asg_SWI;
910 --
911 -- Reset IN OUT parameters and set OUT parameters
912 --
913 p_return_status := Hr_Multi_Message.get_return_status_disable;
914 Hr_Utility.set_location(' Leaving:' || l_proc, 70);
915 WHEN Others THEN
916 --
917 -- When Multiple Message Detection is enabled catch
918 -- any Application specific or other unexpected
919 -- exceptions. Adding appropriate details to the
920 -- Multiple Message List. Otherwise re-raise the
921 -- error.
922 --
923 ROLLBACK TO Upd_OSS_Person_Asg_SWI;
924 IF Hr_Multi_Message.unexpected_error_add(l_proc) THEN
925 Hr_Utility.set_location(' Leaving:' || l_proc,80);
926 END IF;
927 --
928 -- Reset IN OUT and set OUT parameters
929 --
930 p_return_status := Hr_Multi_Message.get_return_status_disable;
931 Hr_Utility.set_location(' Leaving:' || l_proc,90);
932
933 END Upd_OSS_Person_Asg;
934
935 -- =============================================================================
936 -- ~ InsUpd_Assig_Extra_Info:
937 -- =============================================================================
938 PROCEDURE InsUpd_Assig_Extra_Info
939 (p_validate IN Number
940 ,p_action IN Varchar2
941 ,p_business_group_id IN Number
942 ,p_effective_date IN Date
943 ,p_assignment_id IN Number
944 ,p_assignment_extra_info_id IN OUT NOCOPY Number
945 ,p_object_version_number IN OUT NOCOPY NUMBER
946 ,p_return_status OUT NOCOPY Varchar2
947 ,p_information_type IN VARCHAR2
948 -- DDF Segments
949 ,p_aei_information_category IN Varchar2
950 ,p_aei_information1 IN Varchar2
951 ,p_aei_information2 IN Varchar2
952 ,p_aei_information3 IN Varchar2
953 ,p_aei_information4 IN Varchar2
954 ,p_aei_information5 IN Varchar2
955 ,p_aei_information6 IN Varchar2
956 ,p_aei_information7 IN Varchar2
957 ,p_aei_information8 IN Varchar2
958 ,p_aei_information9 IN Varchar2
959 ,p_aei_information10 IN Varchar2
960 ,p_aei_information11 IN Varchar2
961 ,p_aei_information12 IN Varchar2
962 ,p_aei_information13 IN Varchar2
963 ,p_aei_information14 IN Varchar2
964 ,p_aei_information15 IN Varchar2
965 ,p_aei_information16 IN Varchar2
966 ,p_aei_information17 IN Varchar2
967 ,p_aei_information18 IN Varchar2
968 ,p_aei_information19 IN Varchar2
969 ,p_aei_information20 IN Varchar2
970 ,p_aei_information21 IN Varchar2
971 ,p_aei_information22 IN Varchar2
972 ,p_aei_information23 IN Varchar2
973 ,p_aei_information24 IN Varchar2
974 ,p_aei_information25 IN Varchar2
975 ,p_aei_information26 IN Varchar2
976 ,p_aei_information27 IN Varchar2
977 ,p_aei_information28 IN Varchar2
978 ,p_aei_information29 IN Varchar2
979 ,p_aei_information30 IN Varchar2
980 -- DF Segments
981 ,p_aei_attribute_category IN Varchar2
982 ,p_aei_attribute1 IN Varchar2
983 ,p_aei_attribute2 IN Varchar2
984 ,p_aei_attribute3 IN Varchar2
985 ,p_aei_attribute4 IN Varchar2
986 ,p_aei_attribute5 IN Varchar2
987 ,p_aei_attribute6 IN Varchar2
988 ,p_aei_attribute7 IN Varchar2
989 ,p_aei_attribute8 IN Varchar2
990 ,p_aei_attribute9 IN Varchar2
991 ,p_aei_attribute10 IN Varchar2
992 ,p_aei_attribute11 IN Varchar2
993 ,p_aei_attribute12 IN Varchar2
994 ,p_aei_attribute13 IN Varchar2
995 ,p_aei_attribute14 IN Varchar2
996 ,p_aei_attribute15 IN Varchar2
997 ,p_aei_attribute16 IN Varchar2
998 ,p_aei_attribute17 IN Varchar2
999 ,p_aei_attribute18 IN Varchar2
1000 ,p_aei_attribute19 IN Varchar2
1001 ,p_aei_attribute20 IN Varchar2
1002 ) IS
1003 l_proc CONSTANT Varchar2(150) := g_package ||'InsUpd_Assig_Extra_Info';
1004 l_extra_info_rec per_assignment_extra_info%ROWTYPE;
1005 l_validate Boolean;
1006 BEGIN
1007 Hr_Utility.set_location(' Entering:' || l_proc,5);
1008 --
1009 -- Issue a savepoint
1010 --
1011 SAVEPOINT Extra_Info;
1012 l_validate := FALSE;
1013 IF NVL(p_validate,0) <> 0 THEN
1014 l_validate := TRUE;
1015 END IF;
1016 --
1017 -- Initialise Multiple Message Detection
1018 --
1019 Hr_Multi_Message.enable_message_list;
1020 --
1021 -- Call API
1022 --
1023 l_extra_info_rec.information_type := p_information_type;
1024 l_extra_info_rec.aei_information_category := p_aei_information_category;
1025 l_extra_info_rec.aei_attribute_category := p_aei_attribute_category;
1026
1027 l_extra_info_rec.aei_information1 := p_aei_information1;
1028 l_extra_info_rec.aei_information2 := p_aei_information2;
1029 l_extra_info_rec.aei_information3 := p_aei_information3;
1030 l_extra_info_rec.aei_information4 := p_aei_information4;
1031 l_extra_info_rec.aei_information5 := p_aei_information5;
1032 l_extra_info_rec.aei_information6 := p_aei_information6;
1033 l_extra_info_rec.aei_information7 := p_aei_information7;
1034 l_extra_info_rec.aei_information8 := p_aei_information8;
1035 l_extra_info_rec.aei_information9 := p_aei_information9;
1036 l_extra_info_rec.aei_information10 := p_aei_information10;
1037 l_extra_info_rec.aei_information11 := p_aei_information11;
1038 l_extra_info_rec.aei_information12 := p_aei_information12;
1039 l_extra_info_rec.aei_information13 := p_aei_information13;
1040 l_extra_info_rec.aei_information14 := p_aei_information14;
1041 l_extra_info_rec.aei_information15 := p_aei_information15;
1042 l_extra_info_rec.aei_information16 := p_aei_information16;
1043 l_extra_info_rec.aei_information17 := p_aei_information17;
1044 l_extra_info_rec.aei_information18 := p_aei_information18;
1045 l_extra_info_rec.aei_information19 := p_aei_information19;
1046 l_extra_info_rec.aei_information20 := p_aei_information20;
1047 l_extra_info_rec.aei_information21 := p_aei_information21;
1048 l_extra_info_rec.aei_information22 := p_aei_information22;
1049 l_extra_info_rec.aei_information23 := p_aei_information23;
1050 l_extra_info_rec.aei_information24 := p_aei_information24;
1051 l_extra_info_rec.aei_information25 := p_aei_information25;
1052 l_extra_info_rec.aei_information26 := p_aei_information26;
1053 l_extra_info_rec.aei_information27 := p_aei_information27;
1054 l_extra_info_rec.aei_information28 := p_aei_information28;
1055 l_extra_info_rec.aei_information29 := p_aei_information29;
1056 l_extra_info_rec.aei_information30 := p_aei_information30;
1057
1058 l_extra_info_rec.aei_attribute1 := p_aei_attribute1;
1059 l_extra_info_rec.aei_attribute2 := p_aei_attribute2;
1060 l_extra_info_rec.aei_attribute3 := p_aei_attribute3;
1061 l_extra_info_rec.aei_attribute4 := p_aei_attribute4;
1062 l_extra_info_rec.aei_attribute5 := p_aei_attribute5;
1063 l_extra_info_rec.aei_attribute6 := p_aei_attribute6;
1064 l_extra_info_rec.aei_attribute7 := p_aei_attribute7;
1065 l_extra_info_rec.aei_attribute8 := p_aei_attribute8;
1066 l_extra_info_rec.aei_attribute9 := p_aei_attribute9;
1067 l_extra_info_rec.aei_attribute10 := p_aei_attribute10;
1068 l_extra_info_rec.aei_attribute11 := p_aei_attribute11;
1069 l_extra_info_rec.aei_attribute12 := p_aei_attribute12;
1070 l_extra_info_rec.aei_attribute13 := p_aei_attribute13;
1071 l_extra_info_rec.aei_attribute14 := p_aei_attribute14;
1072 l_extra_info_rec.aei_attribute15 := p_aei_attribute15;
1073 l_extra_info_rec.aei_attribute16 := p_aei_attribute16;
1074 l_extra_info_rec.aei_attribute17 := p_aei_attribute17;
1075 l_extra_info_rec.aei_attribute18 := p_aei_attribute18;
1076 l_extra_info_rec.aei_attribute19 := p_aei_attribute19;
1077 l_extra_info_rec.aei_attribute20 := p_aei_attribute20;
1078
1079 IF p_action = 'CREATE' THEN
1080 l_extra_info_rec.assignment_id := Null;
1081 l_extra_info_rec.assignment_extra_info_id := Null;
1082 ELSIF p_action IN('UPDATE','DELETE') THEN
1083 l_extra_info_rec.assignment_id := p_assignment_id;
1084 l_extra_info_rec.assignment_extra_info_id := p_assignment_extra_info_id;
1085 l_extra_info_rec.object_version_number := p_object_version_number;
1086 END IF;
1087 PQP_HRTCA_Integration.InsUpd_Asg_Extra_info
1088 (p_assignment_id => p_assignment_id
1089 ,p_business_group_id => p_business_group_id
1090 ,p_validate => l_validate
1091 ,p_action => p_action
1092 ,p_extra_info_rec => l_extra_info_rec
1093 );
1094 p_assignment_extra_info_id := l_extra_info_rec.assignment_extra_info_id;
1095 p_object_version_number := l_extra_info_rec.object_version_number;
1096
1097 p_return_status := Hr_Multi_Message.get_return_status_disable;
1098 Hr_Utility.set_location(' Leaving:' || l_proc,50);
1099 --
1100 EXCEPTION
1101 WHEN Hr_Multi_Message.error_message_exist THEN
1102 -- Catch the Multiple Message List exception which
1103 -- indicates API processing has been aborted because
1104 -- at least one message exists in the list.
1105 --
1106 ROLLBACK TO Extra_Info;
1107 --
1108 -- Reset IN OUT parameters and set OUT parameters
1109 --
1110 p_return_status := Hr_Multi_Message.get_return_status_disable;
1111 p_assignment_extra_info_id := Null;
1112 p_object_version_number := Null;
1113
1114 Hr_Utility.set_location(' Leaving:' || l_proc, 70);
1115 WHEN Others THEN
1116 -- When Multiple Message Detection is enabled catch
1117 -- any Application specific or other unexpected
1118 -- exceptions. Adding appropriate details to the
1119 -- Multiple Message List. Otherwise re-raise the
1120 -- error.
1121 --
1122 ROLLBACK TO Extra_Info;
1123 IF Hr_Multi_Message.unexpected_error_add(l_proc) THEN
1124 Hr_Utility.set_location(' Leaving:' || l_proc,80);
1125 END IF;
1126 --
1127 -- Reset IN OUT and set OUT parameters
1128 --
1129 p_return_status := Hr_Multi_Message.get_return_status_disable;
1130 p_assignment_extra_info_id := Null;
1131 p_object_version_number := Null;
1132
1133 Hr_Utility.set_location(' Leaving:' || l_proc,90);
1134
1135 END InsUpd_Assig_Extra_Info;
1136 -- =============================================================================
1137 -- ~ InsUpd_Person_Extra_Info:
1138 -- =============================================================================
1139 PROCEDURE InsUpd_Person_Extra_Info
1140 (p_validate IN Number
1141 ,p_action IN Varchar2
1142 ,p_business_group_id IN Number
1143 ,p_effective_date IN Date
1144 ,p_person_id IN Number
1145 ,p_person_extra_info_id IN OUT NOCOPY Number
1146 ,p_object_version_number IN OUT NOCOPY NUMBER
1147 ,p_return_status OUT NOCOPY Varchar2
1148 ,p_information_type IN VARCHAR2
1149 -- DDF Segments
1150 ,p_pei_information_category IN Varchar2
1151 ,p_pei_information1 IN Varchar2
1152 ,p_pei_information2 IN Varchar2
1153 ,p_pei_information3 IN Varchar2
1154 ,p_pei_information4 IN Varchar2
1155 ,p_pei_information5 IN Varchar2
1156 ,p_pei_information6 IN Varchar2
1157 ,p_pei_information7 IN Varchar2
1158 ,p_pei_information8 IN Varchar2
1159 ,p_pei_information9 IN Varchar2
1160 ,p_pei_information10 IN Varchar2
1161 ,p_pei_information11 IN Varchar2
1162 ,p_pei_information12 IN Varchar2
1163 ,p_pei_information13 IN Varchar2
1164 ,p_pei_information14 IN Varchar2
1165 ,p_pei_information15 IN Varchar2
1166 ,p_pei_information16 IN Varchar2
1167 ,p_pei_information17 IN Varchar2
1168 ,p_pei_information18 IN Varchar2
1169 ,p_pei_information19 IN Varchar2
1170 ,p_pei_information20 IN Varchar2
1171 ,p_pei_information21 IN Varchar2
1172 ,p_pei_information22 IN Varchar2
1173 ,p_pei_information23 IN Varchar2
1174 ,p_pei_information24 IN Varchar2
1175 ,p_pei_information25 IN Varchar2
1176 ,p_pei_information26 IN Varchar2
1177 ,p_pei_information27 IN Varchar2
1178 ,p_pei_information28 IN Varchar2
1179 ,p_pei_information29 IN Varchar2
1180 ,p_pei_information30 IN Varchar2
1181 -- DF Segments
1182 ,p_pei_attribute_category IN Varchar2
1183 ,p_pei_attribute1 IN Varchar2
1184 ,p_pei_attribute2 IN Varchar2
1185 ,p_pei_attribute3 IN Varchar2
1186 ,p_pei_attribute4 IN Varchar2
1187 ,p_pei_attribute5 IN Varchar2
1188 ,p_pei_attribute6 IN Varchar2
1189 ,p_pei_attribute7 IN Varchar2
1190 ,p_pei_attribute8 IN Varchar2
1191 ,p_pei_attribute9 IN Varchar2
1192 ,p_pei_attribute10 IN Varchar2
1193 ,p_pei_attribute11 IN Varchar2
1194 ,p_pei_attribute12 IN Varchar2
1195 ,p_pei_attribute13 IN Varchar2
1196 ,p_pei_attribute14 IN Varchar2
1197 ,p_pei_attribute15 IN Varchar2
1198 ,p_pei_attribute16 IN Varchar2
1199 ,p_pei_attribute17 IN Varchar2
1200 ,p_pei_attribute18 IN Varchar2
1201 ,p_pei_attribute19 IN Varchar2
1202 ,p_pei_attribute20 IN Varchar2
1203 ) IS
1204 Cursor csr_pei (p_person_id IN Number
1205 ,p_person_extra_info_id IN Number
1206 ,p_information_type IN Varchar2) Is
1207 SELECT *
1208 FROM per_people_extra_info pei
1209 WHERE pei.person_id = p_person_id
1210 AND pei.person_extra_info_id = p_person_extra_info_id
1211 AND pei.information_type = p_information_type;
1212
1213 l_proc CONSTANT Varchar2(150) := g_package ||'InsUpd_Person_Extra_Info';
1214 l_extra_info_rec per_people_extra_info%ROWTYPE;
1215 l_old_info_rec per_people_extra_info%ROWTYPE;
1216 l_error_msg varchar2(2000);
1217 l_msg_data varchar2(4000);
1218 l_validate Boolean;
1219 BEGIN
1220 Hr_Utility.set_location(' Entering:' || l_proc,5);
1221 --
1222 -- Issue a savepoint
1223 --
1224 SAVEPOINT Extra_Info;
1225 l_validate := FALSE;
1226 IF NVL(p_validate,0) <> 0 THEN
1227 l_validate := TRUE;
1228 END IF;
1229 Hr_Utility.set_location(' set the person id profile:' || p_person_id,7);
1230 Fnd_Profile.put('PER_PERSON_ID',p_person_id);
1231 --
1232 -- Initialise Multiple Message Detection
1233 --
1234 Hr_Multi_Message.enable_message_list;
1235 --
1236 -- Call API
1237 --
1238 l_extra_info_rec.information_type := p_information_type;
1239 l_extra_info_rec.pei_information_category := p_pei_information_category;
1240 l_extra_info_rec.pei_attribute_category := p_pei_attribute_category;
1241
1242 l_extra_info_rec.pei_information1 := p_pei_information1;
1243 l_extra_info_rec.pei_information2 := p_pei_information2;
1244 l_extra_info_rec.pei_information3 := p_pei_information3;
1245 l_extra_info_rec.pei_information4 := p_pei_information4;
1246 l_extra_info_rec.pei_information5 := p_pei_information5;
1247
1248 l_extra_info_rec.pei_information6 := p_pei_information6;
1249 l_extra_info_rec.pei_information7 := p_pei_information7;
1250 l_extra_info_rec.pei_information8 := p_pei_information8;
1251 l_extra_info_rec.pei_information9 := p_pei_information9;
1252 l_extra_info_rec.pei_information10 := p_pei_information10;
1253
1254 l_extra_info_rec.pei_information11 := p_pei_information11;
1255 l_extra_info_rec.pei_information12 := p_pei_information12;
1256 l_extra_info_rec.pei_information13 := p_pei_information13;
1257 l_extra_info_rec.pei_information14 := p_pei_information14;
1258 l_extra_info_rec.pei_information15 := p_pei_information15;
1259
1260 l_extra_info_rec.pei_information16 := p_pei_information16;
1261 l_extra_info_rec.pei_information17 := p_pei_information17;
1262 l_extra_info_rec.pei_information18 := p_pei_information18;
1263 l_extra_info_rec.pei_information19 := p_pei_information19;
1264 l_extra_info_rec.pei_information20 := p_pei_information20;
1265
1266 l_extra_info_rec.pei_information21 := p_pei_information21;
1267 l_extra_info_rec.pei_information22 := p_pei_information22;
1268 l_extra_info_rec.pei_information23 := p_pei_information23;
1269 l_extra_info_rec.pei_information24 := p_pei_information24;
1270 l_extra_info_rec.pei_information25 := p_pei_information25;
1271
1272 l_extra_info_rec.pei_information26 := p_pei_information26;
1273 l_extra_info_rec.pei_information27 := p_pei_information27;
1274 l_extra_info_rec.pei_information28 := p_pei_information28;
1275 l_extra_info_rec.pei_information29 := p_pei_information29;
1276 l_extra_info_rec.pei_information30 := p_pei_information30;
1277
1278 l_extra_info_rec.pei_attribute1 := p_pei_attribute1;
1279 l_extra_info_rec.pei_attribute2 := p_pei_attribute2;
1280 l_extra_info_rec.pei_attribute3 := p_pei_attribute3;
1281 l_extra_info_rec.pei_attribute4 := p_pei_attribute4;
1282 l_extra_info_rec.pei_attribute5 := p_pei_attribute5;
1283
1284 l_extra_info_rec.pei_attribute6 := p_pei_attribute6;
1285 l_extra_info_rec.pei_attribute7 := p_pei_attribute7;
1286 l_extra_info_rec.pei_attribute8 := p_pei_attribute8;
1287 l_extra_info_rec.pei_attribute9 := p_pei_attribute9;
1288 l_extra_info_rec.pei_attribute10 := p_pei_attribute10;
1289
1290 l_extra_info_rec.pei_attribute11 := p_pei_attribute11;
1291 l_extra_info_rec.pei_attribute12 := p_pei_attribute12;
1292 l_extra_info_rec.pei_attribute13 := p_pei_attribute13;
1293 l_extra_info_rec.pei_attribute14 := p_pei_attribute14;
1294 l_extra_info_rec.pei_attribute15 := p_pei_attribute15;
1295
1296 l_extra_info_rec.pei_attribute16 := p_pei_attribute16;
1297 l_extra_info_rec.pei_attribute17 := p_pei_attribute17;
1298 l_extra_info_rec.pei_attribute18 := p_pei_attribute18;
1299 l_extra_info_rec.pei_attribute19 := p_pei_attribute19;
1300 l_extra_info_rec.pei_attribute20 := p_pei_attribute20;
1301
1302 IF p_action = 'CREATE' THEN
1303 l_extra_info_rec.person_id := Null;
1304 l_extra_info_rec.person_extra_info_id := Null;
1305 IF (l_extra_info_rec.information_type
1306 ='PER_US_VISIT_HISTORY') THEN
1307 IF(Get_PK_For_Validation
1308 (l_extra_info_rec.pei_information13
1309 ,l_extra_info_rec.pei_information12)
1310 ) THEN
1311 l_msg_data
1312 := fnd_message.get_string('PQP','PQP_230198_VISA_HISTORY_EXISTS');
1313 END IF;
1314 END IF;
1315
1316 ELSIF p_action = 'UPDATE' THEN
1317 -- If the action is update
1318 l_extra_info_rec.person_id := p_person_id;
1319 l_extra_info_rec.person_extra_info_id := p_person_extra_info_id;
1320 l_extra_info_rec.object_version_number := p_object_version_number;
1321 OPEN csr_pei (p_person_id => p_person_id
1322 ,p_person_extra_info_id => p_person_extra_info_id
1323 ,p_information_type => l_extra_info_rec.information_type);
1324 FETCH csr_pei INTO l_old_info_rec;
1325 CLOSE csr_pei;
1326 -- Need to do some additional check for the following information type.
1327 IF l_extra_info_rec.information_type = 'PER_US_VISIT_HISTORY' THEN
1328 -- =================================================================================
1329 -- Purpose (VS)-R = pei_information5 = This is Specific to HRMS
1330 -- Visa Number = pei_information11 = igs_pe_visit_histry_v.visa_number
1331 -- Start Date-R = pei_information7 = igs_pe_visit_histry.visit_start_date
1332 -- End Date = pei_information8 = igs_pe_visit_histry.visit_end_date
1333 -- Spouse Accompanied-R = pei_information9 = This is Specific to HRMS
1334 -- Child Accompanied -R = pei_information10 = This is Specific to HRMS
1335 -- Entry Number = pei_information12 = igs_pe_visit_histry.cntry_entry_form_num
1336 -- Port Of Entry (VS) = pei_information13 = igs_pe_visit_histry.port_of_entry
1337 -- ================================================================================
1338 IF l_extra_info_rec.pei_information11 Is null THEN
1339 l_msg_data := 'VISA Number is required for student.';
1340 ELSIF (l_extra_info_rec.pei_information12 Is Null or
1341 l_extra_info_rec.pei_information13 Is Null ) THEN
1342 l_msg_data := 'Port Of Entry and Entry Number are required for Student Employee.';
1343 ELSIF (l_extra_info_rec.pei_information12 <>
1344 l_old_info_rec.pei_information12) or
1345 (l_extra_info_rec.pei_information13 <>
1346 l_old_info_rec.pei_information13) THEN
1347 l_msg_data := 'Cannot Update the Port of Entry or Number for a Student Employee.';
1348 END IF;
1349 END IF;
1350
1351 ELSIF p_action = 'DELETE' THEN
1352 -- If the action is delete.
1353 l_extra_info_rec.person_id := p_person_id;
1354 l_extra_info_rec.person_extra_info_id := p_person_extra_info_id;
1355 l_extra_info_rec.object_version_number := p_object_version_number;
1356
1357 END IF;
1358 -- Check if any errors are raised.
1359 IF l_msg_data Is Not Null Then
1360 l_error_msg := Substrb(l_msg_data,1,2000);
1361 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
1362 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
1363 Hr_Utility.raise_error;
1364 END IF;
1365
1366 PQP_HRTCA_Integration.InsUpd_Per_Extra_info
1367 (p_person_id => p_person_id
1368 ,p_business_group_id => p_business_group_id
1369 ,p_validate => l_validate
1370 ,p_action => p_action
1371 ,p_extra_info_rec => l_extra_info_rec
1372 );
1373 p_person_extra_info_id := l_extra_info_rec.person_extra_info_id;
1374 p_object_version_number := l_extra_info_rec.object_version_number;
1375
1376 p_return_status := Hr_Multi_Message.get_return_status_disable;
1377 Hr_Utility.set_location(' Leaving:' || l_proc,50);
1378 --
1379 EXCEPTION
1380 WHEN Hr_Multi_Message.error_message_exist THEN
1381 --
1382 -- Catch the Multiple Message List exception which
1383 -- indicates API processing has been aborted because
1384 -- at least one message exists in the list.
1385 --
1386 ROLLBACK TO Extra_Info;
1387 --
1388 -- Reset IN OUT parameters and set OUT parameters
1389 --
1390 p_return_status := Hr_Multi_Message.get_return_status_disable;
1391 p_person_extra_info_id := Null;
1392 p_object_version_number := Null;
1393
1394 Hr_Utility.set_location(' Leaving:' || l_proc, 70);
1395 WHEN Others THEN
1396 --
1397 -- When Multiple Message Detection is enabled catch
1398 -- any Application specific or other unexpected
1399 -- exceptions. Adding appropriate details to the
1400 -- Multiple Message List. Otherwise re-raise the
1401 -- error.
1402 --
1403 ROLLBACK TO Extra_Info;
1404 IF Hr_Multi_Message.unexpected_error_add(l_proc) THEN
1405 Hr_Utility.set_location(' Leaving:' || l_proc,80);
1406 END IF;
1407 --
1408 -- Reset IN OUT and set OUT parameters
1409 --
1410 p_return_status := Hr_Multi_Message.get_return_status_disable;
1411 p_person_extra_info_id := Null;
1412 p_object_version_number := Null;
1413
1414 Hr_Utility.set_location(' Leaving:' || l_proc,90);
1415
1416 END InsUpd_Person_Extra_Info;
1417 -- =============================================================================
1418 -- ~ InsUpd_SIT_Info:
1419 -- =============================================================================
1420 PROCEDURE InsUpd_SIT_Info
1421 (p_valiDate IN Number
1422 ,p_action IN Varchar2
1423 ,p_person_id IN Number
1424 ,p_business_group_id IN Number
1425 ,p_id_flex_num IN Number
1426 ,p_effective_date IN Date
1427 ,p_Date_from IN Date
1428 ,p_Date_to IN Date
1429 ,p_concat_segments IN varchar2
1430 ,p_analysis_criteria_id IN OUT NOCOPY Number
1431 ,p_person_analysis_id IN OUT NOCOPY Number
1432 ,p_pea_object_version_Number IN OUT NOCOPY Number
1433 ,p_return_status OUT NOCOPY Varchar2
1434 -- DF on per_person_analyses
1435 ,p_attribute_category IN Varchar2
1436 ,p_attribute1 IN Varchar2
1437 ,p_attribute2 IN Varchar2
1438 ,p_attribute3 IN Varchar2
1439 ,p_attribute4 IN Varchar2
1440 ,p_attribute5 IN Varchar2
1441 ,p_attribute6 IN Varchar2
1442 ,p_attribute7 IN Varchar2
1443 ,p_attribute8 IN Varchar2
1444 ,p_attribute9 IN Varchar2
1445 ,p_attribute10 IN Varchar2
1446 ,p_attribute11 IN Varchar2
1447 ,p_attribute12 IN Varchar2
1448 ,p_attribute13 IN Varchar2
1449 ,p_attribute14 IN Varchar2
1450 ,p_attribute15 IN Varchar2
1451 ,p_attribute16 IN Varchar2
1452 ,p_attribute17 IN Varchar2
1453 ,p_attribute18 IN Varchar2
1454 ,p_attribute19 IN Varchar2
1455 ,p_attribute20 IN Varchar2
1456 -- KFF segments on per_analysis_criteria
1457 ,p_segment1 IN Varchar2
1458 ,p_segment2 IN Varchar2
1459 ,p_segment3 IN Varchar2
1460 ,p_segment4 IN Varchar2
1461 ,p_segment5 IN Varchar2
1462 ,p_segment6 IN Varchar2
1463 ,p_segment7 IN Varchar2
1464 ,p_segment8 IN Varchar2
1465 ,p_segment9 IN Varchar2
1466 ,p_segment10 IN Varchar2
1467 ,p_segment11 IN Varchar2
1468 ,p_segment12 IN Varchar2
1469 ,p_segment13 IN Varchar2
1470 ,p_segment14 IN Varchar2
1471 ,p_segment15 IN Varchar2
1472 ,p_segment16 IN Varchar2
1473 ,p_segment17 IN Varchar2
1474 ,p_segment18 IN Varchar2
1475 ,p_segment19 IN Varchar2
1476 ,p_segment20 IN Varchar2
1477 ,p_segment21 IN Varchar2
1478 ,p_segment22 IN Varchar2
1479 ,p_segment23 IN Varchar2
1480 ,p_segment24 IN Varchar2
1481 ,p_segment25 IN Varchar2
1482 ,p_segment26 IN Varchar2
1483 ,p_segment27 IN Varchar2
1484 ,p_segment28 IN Varchar2
1485 ,p_segment29 IN Varchar2
1486 ,p_segment30 IN Varchar2
1487 --
1488 ,p_comments IN varchar2
1489 ,p_request_id IN Number
1490 ,p_program_application_id IN Number
1491 ,p_program_id IN Number
1492 ,p_program_update_date IN Date
1493 ) IS
1494
1495 l_proc CONSTANT Varchar2(150) := g_package ||'InsUpd_SIT_Info';
1496 l_analysis_criteria_rec per_analysis_criteria%ROWTYPE;
1497 l_analyses_rec per_person_analyses%ROWTYPE;
1498 l_validate Boolean;
1499 BEGIN
1500 Hr_Utility.set_location(' Entering:' || l_proc,5);
1501 --
1502 -- Issue a savepoint
1503 --
1504 SAVEPOINT Extra_Info;
1505 l_validate := FALSE;
1506 IF NVL(p_validate,0) <> 0 THEN
1507 l_validate := TRUE;
1508 END IF;
1509
1510 --
1511 -- Initialise Multiple Message Detection
1512 --
1513 Hr_Multi_Message.enable_message_list;
1514 --
1515 -- Call API
1516 --
1517 l_analyses_rec.business_group_id := p_business_group_id;
1518 l_analyses_rec.person_id := p_person_id;
1519 l_analyses_rec.id_flex_num := p_id_flex_num;
1520 l_analyses_rec.Date_from := p_Date_from;
1521 l_analyses_rec.Date_to := p_Date_to;
1522 l_analyses_rec.object_version_Number := p_pea_object_version_Number;
1523 l_analyses_rec.person_analysis_id := p_person_analysis_id;
1524
1525 l_analyses_rec.attribute_category := p_attribute_category;
1526 l_analyses_rec.attribute1 := p_attribute1;
1527 l_analyses_rec.attribute2 := p_attribute2;
1528 l_analyses_rec.attribute3 := p_attribute3;
1529 l_analyses_rec.attribute4 := p_attribute4;
1530 l_analyses_rec.attribute5 := p_attribute5;
1531 l_analyses_rec.attribute6 := p_attribute6;
1532 l_analyses_rec.attribute7 := p_attribute7;
1533 l_analyses_rec.attribute8 := p_attribute8;
1534 l_analyses_rec.attribute9 := p_attribute9;
1535 l_analyses_rec.attribute10 := p_attribute10;
1536 l_analyses_rec.attribute11 := p_attribute11;
1537 l_analyses_rec.attribute12 := p_attribute12;
1538 l_analyses_rec.attribute13 := p_attribute13;
1539 l_analyses_rec.attribute14 := p_attribute14;
1540 l_analyses_rec.attribute15 := p_attribute15;
1541 l_analyses_rec.attribute16 := p_attribute16;
1542 l_analyses_rec.attribute17 := p_attribute17;
1543 l_analyses_rec.attribute18 := p_attribute18;
1544 l_analyses_rec.attribute19 := p_attribute19;
1545 l_analyses_rec.attribute20 := p_attribute20;
1546
1547 l_analysis_criteria_rec.analysis_criteria_id := p_analysis_criteria_id;
1548 l_analysis_criteria_rec.segment1 := p_segment1;
1549 l_analysis_criteria_rec.segment2 := p_segment2;
1550 l_analysis_criteria_rec.segment3 := p_segment3;
1551 l_analysis_criteria_rec.segment4 := p_segment4;
1552 l_analysis_criteria_rec.segment5 := p_segment5;
1553 l_analysis_criteria_rec.segment6 := p_segment6;
1554 l_analysis_criteria_rec.segment7 := p_segment7;
1555 l_analysis_criteria_rec.segment8 := p_segment8;
1556 l_analysis_criteria_rec.segment9 := p_segment9;
1557 l_analysis_criteria_rec.segment10 := p_segment10;
1558 l_analysis_criteria_rec.segment11 := p_segment11;
1559 l_analysis_criteria_rec.segment12 := p_segment12;
1560 l_analysis_criteria_rec.segment13 := p_segment13;
1561 l_analysis_criteria_rec.segment14 := p_segment14;
1562 l_analysis_criteria_rec.segment15 := p_segment15;
1563 l_analysis_criteria_rec.segment16 := p_segment16;
1564 l_analysis_criteria_rec.segment17 := p_segment17;
1565 l_analysis_criteria_rec.segment18 := p_segment18;
1566 l_analysis_criteria_rec.segment19 := p_segment19;
1567 l_analysis_criteria_rec.segment20 := p_segment20;
1568 l_analysis_criteria_rec.segment21 := p_segment21;
1569 l_analysis_criteria_rec.segment22 := p_segment22;
1570 l_analysis_criteria_rec.segment23 := p_segment23;
1571 l_analysis_criteria_rec.segment24 := p_segment24;
1572 l_analysis_criteria_rec.segment25 := p_segment25;
1573 l_analysis_criteria_rec.segment26 := p_segment26;
1574 l_analysis_criteria_rec.segment27 := p_segment27;
1575 l_analysis_criteria_rec.segment28 := p_segment28;
1576 l_analysis_criteria_rec.segment29 := p_segment29;
1577 l_analysis_criteria_rec.segment30 := p_segment30;
1578
1579 IF p_action = 'CREATE' THEN
1580 l_analyses_rec.person_analysis_id := NULL;
1581 l_analyses_rec.object_version_Number := NULL;
1582 END IF;
1583
1584 PQP_HRTCA_Integration.InsUpd_SIT_info
1585 (p_person_id => p_person_id
1586 ,p_business_group_id => p_business_group_id
1587 ,p_validate => l_validate
1588 ,p_effective_date => p_effective_date
1589 ,p_action => p_action
1590 ,p_analysis_criteria_rec => l_analysis_criteria_rec
1591 ,p_analyses_rec => l_analyses_rec
1592 );
1593 p_analysis_criteria_id := l_analysis_criteria_rec.analysis_criteria_id;
1594 p_person_analysis_id := l_analyses_rec.person_analysis_id;
1595 p_pea_object_version_Number := l_analyses_rec.object_version_Number;
1596
1597
1598 p_return_status := Hr_Multi_Message.get_return_status_disable;
1599 Hr_Utility.set_location(' Leaving:' || l_proc,50);
1600 --
1601 EXCEPTION
1602 WHEN Hr_Multi_Message.error_message_exist THEN
1603 --
1604 -- Catch the Multiple Message List exception which
1605 -- indicates API processing has been aborted because
1606 -- at least one message exists in the list.
1607 --
1608 ROLLBACK TO Extra_Info;
1609
1610 --
1611 -- Reset IN OUT parameters and set OUT parameters
1612 --
1613 p_return_status := Hr_Multi_Message.get_return_status_disable;
1614 p_analysis_criteria_id := Null;
1615 p_person_analysis_id := Null;
1616 p_pea_object_version_Number := Null;
1617
1618 Hr_Utility.set_location(' Leaving:' || l_proc, 70);
1619 WHEN Others THEN
1620 --
1621 -- When Multiple Message Detection is enabled catch
1622 -- any Application specific or other unexpected
1623 -- exceptions. Adding appropriate details to the
1624 -- Multiple Message List. Otherwise re-raise the
1625 -- error.
1626 --
1627 ROLLBACK TO Extra_Info;
1628 IF Hr_Multi_Message.unexpected_error_add(l_proc) THEN
1629 Hr_Utility.set_location(' Leaving:' || l_proc,80);
1630 END IF;
1631 --
1632 -- Reset IN OUT and set OUT parameters
1633 --
1634 p_return_status := Hr_Multi_Message.get_return_status_disable;
1635 p_analysis_criteria_id := Null;
1636 p_person_analysis_id := Null;
1637 p_pea_object_version_Number := Null;
1638 Hr_Utility.set_location(' Leaving:' || l_proc,90);
1639
1640 END InsUpd_SIT_Info;
1641
1642 END PQP_HROSS_Integration_SWI;