[Home] [Help]
PACKAGE BODY: APPS.PER_RI_CREATE_CRP_EMPLOYEE
Source
1 Package Body per_ri_create_crp_employee As
2 /* $Header: pericemp.pkb 120.3 2011/12/21 18:57:54 pparate ship $ */
3 Function get_line_status(p_view Varchar2,p_dp_batch_line_id Number)
4 Return Varchar2 Is
5
6 Type csr_dp_line_status_type Is Ref Cursor;
7 csr_dp_line_status csr_dp_line_status_type;
8
9 l_status Varchar2(2);
10 l_sql_stmt Varchar2(200);
11 Begin
12
13 l_sql_stmt := 'Select line_status From '||p_view||' Where batch_line_id = :1';
14
15 Open csr_dp_line_status For l_sql_stmt using p_dp_batch_line_id;
16 Fetch csr_dp_line_status Into l_status;
17 Close csr_dp_line_status;
18
19 return l_status;
20
21 End get_line_status;
22
23
24 Procedure insert_batch_lines(p_batch_id In Number
25 ,p_data_pump_batch_line_id In Varchar2 Default Null
26 ,p_user_sequence In Number Default Null
27 ,p_link_value In Number Default Null
28 ,p_hire_date In Date
29 ,p_last_name In Varchar2
30 ,p_sex In Varchar2
31 ,p_per_comments In Varchar2 Default Null
32 ,p_date_employee_data_verified In Date Default Null
33 ,p_date_of_birth In Date Default Null
34 ,p_email_address In Varchar2 Default Null
35 ,p_employee_number In Varchar2 Default Null
36 ,p_expense_check_send_to_addres In Varchar2 Default Null
37 ,p_first_name In Varchar2 Default Null
38 ,p_known_as In Varchar2 Default Null
39 ,p_marital_status In Varchar2 Default Null
40 ,p_middle_names In Varchar2 Default Null
41 ,p_nationality In Varchar2 Default Null
42 ,p_national_identifier In Varchar2 Default Null
43 ,p_previous_last_name In Varchar2 Default Null
44 ,p_registered_disabled_flag In Varchar2 Default Null
45 ,p_title In Varchar2 Default Null
46 ,p_work_telephone In Varchar2 Default Null
47 ,p_attribute_category In Varchar2 Default Null
48 ,p_attribute1 In Varchar2 Default Null
49 ,p_attribute2 In Varchar2 Default Null
50 ,p_attribute3 In Varchar2 Default Null
51 ,p_attribute4 In Varchar2 Default Null
52 ,p_attribute5 In Varchar2 Default Null
53 ,p_attribute6 In Varchar2 Default Null
54 ,p_attribute7 In Varchar2 Default Null
55 ,p_attribute8 In Varchar2 Default Null
56 ,p_attribute9 In Varchar2 Default Null
57 ,p_attribute10 In Varchar2 Default Null
58 ,p_attribute11 In Varchar2 Default Null
59 ,p_attribute12 In Varchar2 Default Null
60 ,p_attribute13 In Varchar2 Default Null
61 ,p_attribute14 In Varchar2 Default Null
62 ,p_attribute15 In Varchar2 Default Null
63 ,p_attribute16 In Varchar2 Default Null
64 ,p_attribute17 In Varchar2 Default Null
65 ,p_attribute18 In Varchar2 Default Null
66 ,p_attribute19 In Varchar2 Default Null
67 ,p_attribute20 In Varchar2 Default Null
68 ,p_attribute21 In Varchar2 Default Null
69 ,p_attribute22 In Varchar2 Default Null
70 ,p_attribute23 In Varchar2 Default Null
71 ,p_attribute24 In Varchar2 Default Null
75 ,p_attribute28 In Varchar2 Default Null
72 ,p_attribute25 In Varchar2 Default Null
73 ,p_attribute26 In Varchar2 Default Null
74 ,p_attribute27 In Varchar2 Default Null
76 ,p_attribute29 In Varchar2 Default Null
77 ,p_attribute30 In Varchar2 Default Null
78 ,p_per_information_category In Varchar2 Default Null
79 ,p_per_information1 In Varchar2 Default Null
80 ,p_per_information2 In Varchar2 Default Null
81 ,p_per_information3 In Varchar2 Default Null
82 ,p_per_information4 In Varchar2 Default Null
83 ,p_per_information5 In Varchar2 Default Null
84 ,p_per_information6 In Varchar2 Default Null
85 ,p_per_information7 In Varchar2 Default Null
86 ,p_per_information8 In Varchar2 Default Null
87 ,p_per_information9 In Varchar2 Default Null
88 ,p_per_information10 In Varchar2 Default Null
89 ,p_per_information11 In Varchar2 Default Null
90 ,p_per_information12 In Varchar2 Default Null
91 ,p_per_information13 In Varchar2 Default Null
92 ,p_per_information14 In Varchar2 Default Null
93 ,p_per_information15 In Varchar2 Default Null
94 ,p_per_information16 In Varchar2 Default Null
95 ,p_per_information17 In Varchar2 Default Null
96 ,p_per_information18 In Varchar2 Default Null
97 ,p_per_information19 In Varchar2 Default Null
98 ,p_per_information20 In Varchar2 Default Null
99 ,p_per_information21 In Varchar2 Default Null
100 ,p_per_information22 In Varchar2 Default Null
101 ,p_per_information23 In Varchar2 Default Null
102 ,p_per_information24 In Varchar2 Default Null
103 ,p_per_information25 In Varchar2 Default Null
104 ,p_per_information26 In Varchar2 Default Null
105 ,p_per_information27 In Varchar2 Default Null
106 ,p_per_information28 In Varchar2 Default Null
107 ,p_per_information29 In Varchar2 Default Null
108 ,p_per_information30 In Varchar2 Default Null
109 ,p_date_of_death In Date Default Null
110 ,p_background_check_status In Varchar2 Default Null
111 ,p_background_date_check In Date Default Null
112 ,p_blood_type In Varchar2 Default Null
113 ,p_fast_path_employee In Varchar2 Default Null
114 ,p_fte_capacity In Number Default Null
115 ,p_honors In Varchar2 Default Null
116 ,p_internal_location In Varchar2 Default Null
117 ,p_last_medical_test_by In Varchar2 Default Null
118 ,p_last_medical_test_date In Date Default Null
119 ,p_mailstop In Varchar2 Default Null
120 ,p_office_number In Varchar2 Default Null
121 ,p_on_military_service In Varchar2 Default Null
122 ,p_pre_name_adjunct In Varchar2 Default Null
123 ,p_projected_start_date In Date Default Null
124 ,p_resume_exists In Varchar2 Default Null
125 ,p_resume_last_updated In Date Default Null
126 ,p_second_passport_exists In Varchar2 Default Null
127 ,p_student_status In Varchar2 Default Null
128 ,p_work_schedule In Varchar2 Default Null
129 ,p_suffix In Varchar2 Default Null
130 ,p_receipt_of_death_cert_date In Date Default Null
131 ,p_coord_ben_med_pln_no In Varchar2 Default Null
132 ,p_coord_ben_no_cvg_flag In Varchar2 Default Null
133 ,p_coord_ben_med_ext_er In Varchar2 Default Null
134 ,p_coord_ben_med_pl_name In Varchar2 Default Null
135 ,p_coord_ben_med_insr_crr_name In Varchar2 Default Null
136 ,p_coord_ben_med_insr_crr_ident In Varchar2 Default Null
137 ,p_coord_ben_med_cvg_strt_dt In Date Default Null
138 ,p_coord_ben_med_cvg_end_dt In Date Default Null
139 ,p_uses_tobacco_flag In Varchar2 Default Null
143 ,p_adjusted_svc_date In Date Default Null
140 ,p_dpdnt_adoption_date In Date Default Null
141 ,p_dpdnt_vlntry_svce_flag In Varchar2 Default Null
142 ,p_original_date_of_hire In Date Default Null
144 ,p_town_of_birth In Varchar2 Default Null
145 ,p_region_of_birth In Varchar2 Default Null
146 ,p_country_of_birth In Varchar2 Default Null
147 ,p_global_person_id In Varchar2 Default Null
148 ,p_party_id In Number Default Null
149 ,p_person_user_key In Varchar2 Default Null
150 ,p_assignment_user_key In Varchar2 Default Null
151 ,p_user_person_type In Varchar2 Default Null
152 ,p_language_code In Varchar2 Default Null
153 ,p_vendor_name In Varchar2 Default Null
154 ,p_correspondence_language In Varchar2 Default Null
155 ,p_benefit_group In Varchar2 Default Null
156 ,p_data_pump_batch_line_id_add In Number Default Null
157 ,p_effective_date In Date Default Null
158 ,p_pradd_ovlapval_override In Boolean Default Null
159 ,p_validate_county In Boolean Default Null
160 ,p_primary_flag In Varchar2
161 ,p_style In Varchar2
162 ,p_date_from In Date Default Null
163 ,p_date_to In Date Default Null
164 ,p_address_type In Varchar2 Default Null
165 ,p_comments In clob Default Null
166 ,p_address_line1 In Varchar2 Default Null
167 ,p_address_line2 In Varchar2 Default Null
168 ,p_address_line3 In Varchar2 Default Null
169 ,p_town_or_city In Varchar2 Default Null
170 ,p_region_1 In Varchar2 Default Null
171 ,p_region_2 In Varchar2 Default Null
172 ,p_region_3 In Varchar2 Default Null
173 ,p_postal_code In Varchar2 Default Null
174 ,p_telephone_number_1 In Varchar2 Default Null
175 ,p_telephone_number_2 In Varchar2 Default Null
176 ,p_telephone_number_3 In Varchar2 Default Null
177 ,p_addr_attribute_category In Varchar2 Default Null
178 ,p_addr_attribute1 In Varchar2 Default Null
179 ,p_addr_attribute2 In Varchar2 Default Null
180 ,p_addr_attribute3 In Varchar2 Default Null
181 ,p_addr_attribute4 In Varchar2 Default Null
182 ,p_addr_attribute5 In Varchar2 Default Null
183 ,p_addr_attribute6 In Varchar2 Default Null
184 ,p_addr_attribute7 In Varchar2 Default Null
185 ,p_addr_attribute8 In Varchar2 Default Null
186 ,p_addr_attribute9 In Varchar2 Default Null
187 ,p_addr_attribute10 In Varchar2 Default Null
188 ,p_addr_attribute11 In Varchar2 Default Null
189 ,p_addr_attribute12 In Varchar2 Default Null
190 ,p_addr_attribute13 In Varchar2 Default Null
191 ,p_addr_attribute14 In Varchar2 Default Null
192 ,p_addr_attribute15 In Varchar2 Default Null
193 ,p_addr_attribute16 In Varchar2 Default Null
194 ,p_addr_attribute17 In Varchar2 Default Null
195 ,p_addr_attribute18 In Varchar2 Default Null
196 ,p_addr_attribute19 In Varchar2 Default Null
197 ,p_addr_attribute20 In Varchar2 Default Null
198 ,p_add_information13 In Varchar2 Default Null
199 ,p_add_information14 In Varchar2 Default Null
200 ,p_add_information15 In Varchar2 Default Null
201 ,p_add_information16 In Varchar2 Default Null
202 ,p_add_information17 In Varchar2 Default Null
203 ,p_add_information18 In Varchar2 Default Null
204 ,p_add_information19 In Varchar2 Default Null
205 ,p_add_information20 In Varchar2 Default Null
206 ,p_address_user_key In Varchar2 Default Null
207 ,p_country In Varchar2 Default Null
208 ,p_asg_location In Varchar2 Default Null
209 ,p_payroll_name In Varchar2 Default Null
210 ,p_pay_basis In Varchar2 Default Null
211 ,p_gre In Varchar2 Default Null
212 ,p_data_pump_batch_line_id_sal In Number Default Null
213 ,p_change_date In Date Default Null
214 ,p_proposed_salary In Number Default Null
215 ,p_proposal_reason In Varchar2 Default Null
216 ) as
217
218 l_person_user_key Varchar2(240);
219 l_assignment_user_key Varchar2(240);
220 l_address_user_key Varchar2(240);
221 l_temp Varchar2(240);
222
223
224 l_dp_batch_line_id_emp Number;
225 l_dp_batch_line_id_add Number;
226 l_dp_batch_line_id_asg Number;
227 l_dp_batch_line_id_asg_cri Number;
228 l_dp_batch_line_id_sal Number;
229 l_dp_batch_line_id_acc Number;
230 l_link_value Number;
231
232 l_per_information_category Varchar2(240);
233
234 Cursor csr_get_per_user_keys(c_batch_line_id Number) Is
235 Select p_person_user_key, p_assignment_user_key
236 From hrdpv_create_employee
237 Where batch_line_id = c_batch_line_id;
238
239 Cursor csr_get_add_user_key(c_batch_line_id Number) Is
240 Select p_address_user_key
241 From hrdpv_create_person_address
242 Where batch_line_id = c_batch_line_id;
243
244 begin
245
246 select max(nvl(link_value,0)) into l_link_value
247 from hr_pump_batch_lines where batch_id = p_batch_id;
248 l_link_value := l_link_value + 1;
249
250 If p_data_pump_batch_line_id Is Not Null Then
251
252 --get batch line ids
253
254 l_dp_batch_line_id_emp := substr(p_data_pump_batch_line_id,0,instr(p_data_pump_batch_line_id,'X',1,1)-1);
255 l_dp_batch_line_id_add := substr(p_data_pump_batch_line_id,instr(p_data_pump_batch_line_id,'X',1,1)+1,instr(p_data_pump_batch_line_id,'X',1,2)-instr(p_data_pump_batch_line_id,'X',1,1)-1);
256 l_dp_batch_line_id_asg := substr(p_data_pump_batch_line_id,instr(p_data_pump_batch_line_id,'X',1,2)+1,instr(p_data_pump_batch_line_id,'X',1,3)-instr(p_data_pump_batch_line_id,'X',1,2)-1);
257 l_dp_batch_line_id_asg_cri := substr(p_data_pump_batch_line_id,instr(p_data_pump_batch_line_id,'X',1,3)+1,instr(p_data_pump_batch_line_id,'X',1,4)-instr(p_data_pump_batch_line_id,'X',1,3)-1);
258 l_dp_batch_line_id_sal := substr(p_data_pump_batch_line_id,instr(p_data_pump_batch_line_id,'X',1,4)+1,instr(p_data_pump_batch_line_id,'X',1,5)-instr(p_data_pump_batch_line_id,'X',1,4)-1);
259 l_dp_batch_line_id_acc := substr(p_data_pump_batch_line_id,instr(p_data_pump_batch_line_id,'X',1,5)+1);
260
261
262 --get User Keys
263
264 Open csr_get_per_user_keys(l_dp_batch_line_id_emp);
265 Fetch csr_get_per_user_keys Into l_person_user_key,l_assignment_user_key;
266 Close csr_get_per_user_keys;
267
268 Open csr_get_add_user_key(l_dp_batch_line_id_add);
269 Fetch csr_get_add_user_key Into l_address_user_key;
270 Close csr_get_add_user_key;
271
272
273 Else
274
275 select to_char(sysdate,'J')||to_char(sysdate,'HH24MISS')||dbms_utility.get_hash_value(p_last_name||p_sex||p_first_name,0,1000)
276 into l_temp
277 from dual;
278
279 l_person_user_key := 'RI~PER~'||l_temp;
280 l_assignment_user_key := 'RI~ASG~'||l_temp;
281 l_address_user_key := 'RI~ADD~'||l_temp;
282
283 End If;
284
285 If l_dp_batch_line_id_emp Is Null Or get_line_status('HRDPV_CREATE_EMPLOYEE',l_dp_batch_line_id_emp) <> 'C' Then
286
287 -- Added because for all legislations which only have an international address style
288 -- donot have the legislation code same as the context code of the address descr. flex
289 -- and hence all those legislations have to be covered one after another here in this if
290 -- condition and also taken care of in the PerAdiEngine.java file
291 if p_per_information_category = 'AU_GLB' then
292 l_per_information_category := 'AU';
293 l_person_user_key := 'EMP~'||p_first_name || '~' || p_last_name || '~' || p_hire_date;
294 l_assignment_user_key := 'ASG~'||p_first_name || '~' || p_last_name || '~' || p_hire_date;
295 l_address_user_key := 'ADD~'||p_first_name || '~' || p_last_name || '~' || p_hire_date;
296 else
297 l_per_information_category := p_per_information_category;
298 end if;
299
300
301 hrdpp_create_employee.insert_batch_lines( p_batch_id => p_batch_id
302 ,p_data_pump_batch_line_id => l_dp_batch_line_id_emp
303 ,p_user_sequence => 1
304 ,p_link_value => l_link_value
305 ,p_hire_date => p_hire_date
306 ,p_last_name => p_last_name
307 ,p_sex => p_sex
308 ,p_per_comments => p_per_comments
309 ,p_date_employee_data_verified => p_date_employee_data_verified
310 ,p_date_of_birth => p_date_of_birth
311 ,p_email_address => p_email_address
312 ,p_employee_number => p_employee_number
313 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
314 ,p_first_name => p_first_name
315 ,p_known_as => p_known_as
316 ,p_marital_status => p_marital_status
317 ,p_middle_names => p_middle_names
318 ,p_nationality => p_nationality
319 ,p_national_identifier => p_national_identifier
320 ,p_previous_last_name => p_previous_last_name
321 ,p_registered_disabled_flag => p_registered_disabled_flag
322 ,p_title => p_title
323 ,p_work_telephone => p_work_telephone
324 ,p_attribute_category => p_attribute_category
328 ,p_attribute4 => p_attribute4
325 ,p_attribute1 => p_attribute1
326 ,p_attribute2 => p_attribute2
327 ,p_attribute3 => p_attribute3
329 ,p_attribute5 => p_attribute5
330 ,p_attribute6 => p_attribute6
331 ,p_attribute7 => p_attribute7
332 ,p_attribute8 => p_attribute8
333 ,p_attribute9 => p_attribute9
334 ,p_attribute10 => p_attribute10
335 ,p_attribute11 => p_attribute11
336 ,p_attribute12 => p_attribute12
337 ,p_attribute13 => p_attribute13
338 ,p_attribute14 => p_attribute14
339 ,p_attribute15 => p_attribute15
340 ,p_attribute16 => p_attribute16
341 ,p_attribute17 => p_attribute17
342 ,p_attribute18 => p_attribute18
343 ,p_attribute19 => p_attribute19
344 ,p_attribute20 => p_attribute20
345 ,p_attribute21 => p_attribute21
346 ,p_attribute22 => p_attribute22
347 ,p_attribute23 => p_attribute23
348 ,p_attribute24 => p_attribute24
349 ,p_attribute25 => p_attribute25
350 ,p_attribute26 => p_attribute26
351 ,p_attribute27 => p_attribute27
352 ,p_attribute28 => p_attribute28
353 ,p_attribute29 => p_attribute29
354 ,p_attribute30 => p_attribute30
355 ,p_per_information_category => l_per_information_category
356 ,p_per_information1 => p_per_information1
357 ,p_per_information2 => p_per_information2
358 ,p_per_information3 => p_per_information3
359 ,p_per_information4 => p_per_information4
360 ,p_per_information5 => p_per_information5
361 ,p_per_information6 => p_per_information6
362 ,p_per_information7 => p_per_information7
363 ,p_per_information8 => p_per_information8
364 ,p_per_information9 => p_per_information9
365 ,p_per_information10 => p_per_information10
366 ,p_per_information11 => p_per_information11
367 ,p_per_information12 => p_per_information12
368 ,p_per_information13 => p_per_information13
369 ,p_per_information14 => p_per_information14
370 ,p_per_information15 => p_per_information15
371 ,p_per_information16 => p_per_information16
372 ,p_per_information17 => p_per_information17
373 ,p_per_information18 => p_per_information18
374 ,p_per_information19 => p_per_information19
375 ,p_per_information20 => p_per_information20
376 ,p_per_information21 => p_per_information21
377 ,p_per_information22 => p_per_information22
378 ,p_per_information23 => p_per_information23
379 ,p_per_information24 => p_per_information24
380 ,p_per_information25 => p_per_information25
381 ,p_per_information26 => p_per_information26
382 ,p_per_information27 => p_per_information27
383 ,p_per_information28 => p_per_information28
384 ,p_per_information29 => p_per_information29
385 ,p_per_information30 => p_per_information30
386 ,p_date_of_death => p_date_of_death
390 ,p_fast_path_employee => p_fast_path_employee
387 ,p_background_check_status => p_background_check_status
388 ,p_background_date_check => p_background_date_check
389 ,p_blood_type => p_blood_type
391 ,p_fte_capacity => p_fte_capacity
392 ,p_honors => p_honors
393 ,p_internal_location => p_internal_location
394 ,p_last_medical_test_by => p_last_medical_test_by
395 ,p_last_medical_test_date => p_last_medical_test_date
396 ,p_mailstop => p_mailstop
397 ,p_office_number => p_office_number
398 ,p_on_military_service => p_on_military_service
399 ,p_pre_name_adjunct => p_pre_name_adjunct
400 ,p_projected_start_date => p_projected_start_date
401 ,p_resume_exists => p_resume_exists
402 ,p_resume_last_updated => p_resume_last_updated
403 ,p_second_passport_exists => null
404 ,p_student_status => null
405 ,p_work_schedule => null
406 ,p_suffix => p_suffix
407 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
408 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
409 ,p_coord_ben_no_cvg_flag => null
410 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
411 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
412 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
413 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
414 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
415 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
416 ,p_uses_tobacco_flag => p_uses_tobacco_flag
417 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
418 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
419 ,p_original_date_of_hire => p_original_date_of_hire
420 ,p_adjusted_svc_date => p_adjusted_svc_date
421 ,p_town_of_birth => p_town_of_birth
422 ,p_region_of_birth => p_region_of_birth
423 ,p_country_of_birth => p_country_of_birth
424 ,p_global_person_id => p_global_person_id
425 ,p_party_id => p_party_id
426 ,p_person_user_key => l_person_user_key
427 ,p_assignment_user_key => l_assignment_user_key
428 ,p_user_person_type => p_user_person_type
429 ,p_language_code => p_language_code
430 ,p_vendor_name => p_vendor_name
431 ,p_correspondence_language => p_correspondence_language
432 ,p_benefit_group => p_benefit_group
433 );
434
435
436 End If;
437
438
439 If l_dp_batch_line_id_add Is Null Or get_line_status('HRDPV_CREATE_PERSON_ADDRESS',l_dp_batch_line_id_add) <> 'C' Then
440
441 hrdpp_create_person_address.insert_batch_lines( p_batch_id => p_batch_id
442 ,p_data_pump_batch_line_id => l_dp_batch_line_id_add
443 ,p_user_sequence => 2
444 ,p_link_value => l_link_value
445 ,p_effective_date => p_hire_date
446 ,p_pradd_ovlapval_override => p_pradd_ovlapval_override
447 ,p_validate_county => p_validate_county
448 ,p_primary_flag => p_primary_flag
449 ,p_style => p_style
450 ,p_date_from => p_hire_date
451 ,p_date_to => p_date_to
452 ,p_address_type => p_address_type
456 ,p_address_line3 => p_address_line3
453 ,p_comments => p_comments
454 ,p_address_line1 => p_address_line1
455 ,p_address_line2 => p_address_line2
457 ,p_town_or_city => p_town_or_city
458 ,p_region_1 => p_region_1
459 ,p_region_2 => p_region_2
460 ,p_region_3 => p_region_3
461 ,p_postal_code => p_postal_code
462 ,p_telephone_number_1 => p_telephone_number_1
463 ,p_telephone_number_2 => p_telephone_number_2
464 ,p_telephone_number_3 => p_telephone_number_3
465 ,p_addr_attribute_category => p_addr_attribute_category
466 ,p_addr_attribute1 => p_addr_attribute1
467 ,p_addr_attribute2 => p_addr_attribute2
468 ,p_addr_attribute3 => p_addr_attribute3
469 ,p_addr_attribute4 => p_addr_attribute4
470 ,p_addr_attribute5 => p_addr_attribute5
471 ,p_addr_attribute6 => p_addr_attribute6
472 ,p_addr_attribute7 => p_addr_attribute7
473 ,p_addr_attribute8 => p_addr_attribute8
474 ,p_addr_attribute9 => p_addr_attribute9
475 ,p_addr_attribute10 => p_addr_attribute10
476 ,p_addr_attribute11 => p_addr_attribute11
477 ,p_addr_attribute12 => p_addr_attribute12
478 ,p_addr_attribute13 => p_addr_attribute13
479 ,p_addr_attribute14 => p_addr_attribute14
480 ,p_addr_attribute15 => p_addr_attribute15
481 ,p_addr_attribute16 => p_addr_attribute16
482 ,p_addr_attribute17 => p_addr_attribute17
483 ,p_addr_attribute18 => p_addr_attribute18
484 ,p_addr_attribute19 => p_addr_attribute19
485 ,p_addr_attribute20 => p_addr_attribute20
486 ,p_add_information13 => p_add_information13
487 ,p_add_information14 => p_add_information14
488 ,p_add_information15 => p_add_information15
489 ,p_add_information16 => p_add_information16
490 ,p_add_information17 => p_add_information17
491 ,p_add_information18 => p_add_information18
492 ,p_add_information19 => p_add_information19
493 ,p_add_information20 => p_add_information20
494 ,p_party_id => p_party_id
495 ,p_address_user_key => l_address_user_key
496 ,p_person_user_key => l_person_user_key
497 ,p_country => p_country
498 );
499 End If;
500
501 If (p_gre Is Not Null) AND (l_dp_batch_line_id_asg Is Null Or get_line_status('HRDPV_UPDATE_EMP_ASG',l_dp_batch_line_id_asg) <> 'C') Then
502
503 hrdpp_update_emp_asg.insert_batch_lines(p_batch_id => p_batch_id
504 ,p_data_pump_batch_line_id => l_dp_batch_line_id_asg
505 ,p_user_sequence => 3
506 ,p_link_value => l_link_value
507 ,p_effective_date => p_hire_date
508 ,p_datetrack_update_mode => 'CORRECTION'
509 ,p_cagr_grade_def_id => null
510 ,p_assignment_user_key => l_assignment_user_key
511 ,p_con_seg_user_name => null
512 ,p_segment1 => p_gre
513 );
514 End If;
515
516
517 If (p_payroll_name Is Not Null or p_asg_location Is Not Null Or p_pay_basis Is Not Null)
518 AND (l_dp_batch_line_id_asg_cri Is Null Or get_line_status('HRDPV_UPDATE_EMP_ASG_CRITERIA',l_dp_batch_line_id_asg_cri) <> 'C') Then
519
520 hrdpp_update_emp_asg_criteria.insert_batch_lines(p_batch_id => p_batch_id
521 ,p_data_pump_batch_line_id => l_dp_batch_line_id_asg_cri
525 ,p_datetrack_update_mode => 'CORRECTION'
522 ,p_user_sequence => 4
523 ,p_link_value => l_link_value
524 ,p_effective_date => p_hire_date
526 ,p_special_ceiling_step_id => null
527 ,p_people_group_id => null
528 ,p_assignment_user_key => l_assignment_user_key
529 ,p_payroll_name => p_payroll_name
530 ,p_location_code => p_asg_location
531 ,p_language_code => userenv('lang')
532 ,p_pay_basis_name => p_pay_basis
533 ,p_con_seg_user_name => null
534 );
535
536 End If;
537
538
539 If (p_proposed_salary Is Not Null or p_change_date Is Not Null Or p_proposal_reason Is Not Null )
540 AND (l_dp_batch_line_id_sal Is Null Or get_line_status('HRDPV_UPLOAD_SALARY_PROPOSAL',l_dp_batch_line_id_sal) <> 'C') Then
541
542 hrdpp_upload_salary_proposal.insert_batch_lines(p_batch_id => p_batch_id
543 ,p_data_pump_batch_line_id => l_dp_batch_line_id_sal
544 ,p_user_sequence => 5
545 ,p_link_value => l_link_value
546 ,p_change_date => p_change_date
547 ,p_proposed_salary => p_proposed_salary
548 ,p_proposal_reason => p_proposal_reason
549 ,p_pay_proposal_id => null
550 ,p_object_version_number => null
551 ,p_component_reason_1 => null
552 ,p_approved_1 => null
553 ,p_component_id_1 => null
554 ,p_ppc_object_version_number_1 => null
555 ,p_component_reason_2 => null
556 ,p_approved_2 => null
557 ,p_component_id_2 => null
558 ,p_ppc_object_version_number_2 => null
559 ,p_component_reason_3 => null
560 ,p_approved_3 => null
561 ,p_component_id_3 => null
562 ,p_ppc_object_version_number_3 => null
563 ,p_component_reason_4 => null
564 ,p_approved_4 => null
565 ,p_component_id_4 => null
566 ,p_ppc_object_version_number_4 => null
567 ,p_component_reason_5 => null
568 ,p_approved_5 => null
569 ,p_component_id_5 => null
570 ,p_ppc_object_version_number_5 => null
571 ,p_component_reason_6 => null
572 ,p_approved_6 => null
573 ,p_component_id_6 => null
574 ,p_ppc_object_version_number_6 => null
575 ,p_component_reason_7 => null
576 ,p_approved_7 => null
577 ,p_component_id_7 => null
578 ,p_ppc_object_version_number_7 => null
579 ,p_component_reason_8 => null
580 ,p_approved_8 => null
581 ,p_component_id_8 => null
582 ,p_ppc_object_version_number_8 => null
583 ,p_component_reason_9 => null
584 ,p_approved_9 => null
585 ,p_component_id_9 => null
586 ,p_ppc_object_version_number_9 => null
587 ,p_component_reason_10 => null
588 ,p_approved_10 => null
589 ,p_component_id_10 => null
590 ,p_ppc_object_version_number_10 => null
591 ,p_assignment_user_key => l_assignment_user_key
592 );
593 End If;
594
595
596 If l_dp_batch_line_id_acc Is Null Or get_line_status('HRDPV_CREATE_USER_ACCT',l_dp_batch_line_id_acc) <> 'C' Then
597
598 hrdpp_create_user_acct.insert_batch_lines(p_batch_id => p_batch_id
599 ,p_data_pump_batch_line_id => l_dp_batch_line_id_acc
600 ,p_link_value => l_link_value
601 ,p_per_effective_start_date => p_hire_date
602 ,p_user_sequence => 6
603 ,p_hire_date => p_hire_date
604 ,p_date_from => p_hire_date
605 ,p_person_user_key => l_person_user_key
606 );
607
608 End If;
609
610 End insert_batch_lines;
611
612 Procedure set_user_acct_details(
613 p_person_id In Number
614 ,p_per_effective_start_date In Date
615 ,p_per_effective_end_date In Date
616 ,p_assignment_id In Number
617 ,p_asg_effective_start_date In Date
618 ,p_asg_effective_end_date In Date
619 ,p_business_group_id In Number
620 ,p_date_from In Date
621 ,p_date_to In Date
622 ,p_org_structure_id In Number
623 ,p_org_structure_vers_id In Number
624 ,p_parent_org_id In Number
625 ,p_single_org_id In Number
626 ,p_run_type In Varchar2
627 ,p_hire_date In Date) Is
628
629 Cursor csr_emp_details Is
630 Select emp.last_name
631 ,emp.first_name
632 ,emp.full_name
633 ,emp.email_address
634 From per_people_f emp
635 Where emp.person_id = p_person_id
636 and emp.effective_start_date = p_per_effective_start_date
637 and emp.effective_end_date = nvl(p_per_effective_end_date,emp.effective_end_date);
638
639 Cursor csr_responsibility_details Is
640 Select res.application_id
641 ,res.responsibility_id
642 From fnd_responsibility res
643 Where res.responsibility_key = 'EMPLOYEE_DIRECT_ACCESS_V4.0';
644
645 l_last_name Varchar2(150);
646 l_first_name Varchar2(150);
647 l_full_name Varchar2(240);
648 l_email_address Varchar2(240);
649
650 l_application_id Number;
651 l_responsibility_id Number;
652
653 Begin
654
655 Open csr_emp_details;
656 Fetch csr_emp_details Into l_last_name, l_first_name, l_full_name, l_email_address;
657 Close csr_emp_details;
658
659 hr_user_acct_utility.g_fnd_user_rec.user_name := l_first_name||'.'||l_last_name;
660 hr_user_acct_utility.g_fnd_user_rec.password := 'Welcome';
661 hr_user_acct_utility.g_fnd_user_rec.email_address := l_email_address;
662 hr_user_acct_utility.g_fnd_user_rec.password_date := p_hire_date;
663 hr_user_acct_utility.g_fnd_user_rec.employee_id := p_person_id;
664
665 Open csr_responsibility_details;
666 Fetch csr_responsibility_details Into l_application_id, l_responsibility_id;
667 Close csr_responsibility_details;
668
669 hr_user_acct_utility.g_fnd_resp_tbl(1).existing_resp_id := l_responsibility_id;
670 hr_user_acct_utility.g_fnd_resp_tbl(1).existing_resp_key := 'EMPLOYEE_DIRECT_ACCESS_V4.0';
671 hr_user_acct_utility.g_fnd_resp_tbl(1).existing_resp_app_id := l_application_id;
672 hr_user_acct_utility.g_fnd_resp_tbl(1).sec_group_id := 0;
673 hr_user_acct_utility.g_fnd_resp_tbl(1).sec_profile_id := 0;
674 hr_user_acct_utility.g_fnd_resp_tbl(1).user_resp_start_date := p_hire_date;
675
676 End set_user_acct_details;
677
678 End per_ri_create_crp_employee;