[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.1 2006/02/27 23:33:48 balchand noship $ */
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
72 ,p_attribute25 In Varchar2 Default Null
73 ,p_attribute26 In Varchar2 Default Null
74 ,p_attribute27 In Varchar2 Default Null
75 ,p_attribute28 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
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
143 ,p_adjusted_svc_date 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 Long 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
217
214 ,p_proposed_salary In Number Default Null
215 ,p_proposal_reason In Varchar2 Default Null
216 ) as
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
231 l_per_information_category Varchar2(240);
232
233 Cursor csr_get_per_user_keys(c_batch_line_id Number) Is
234 Select p_person_user_key, p_assignment_user_key
235 From hrdpv_create_employee
236 Where batch_line_id = c_batch_line_id;
237
238 Cursor csr_get_add_user_key(c_batch_line_id Number) Is
239 Select p_address_user_key
240 From hrdpv_create_person_address
241 Where batch_line_id = c_batch_line_id;
242
243 begin
244
245 If p_data_pump_batch_line_id Is Not Null Then
246
247 --get batch line ids
248
249 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);
250 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);
251 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);
252 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);
253 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);
254 l_dp_batch_line_id_acc := substr(p_data_pump_batch_line_id,instr(p_data_pump_batch_line_id,'X',1,5)+1);
255
256
257 --get User Keys
258
259 Open csr_get_per_user_keys(l_dp_batch_line_id_emp);
260 Fetch csr_get_per_user_keys Into l_person_user_key,l_assignment_user_key;
261 Close csr_get_per_user_keys;
262
263 Open csr_get_add_user_key(l_dp_batch_line_id_add);
264 Fetch csr_get_add_user_key Into l_address_user_key;
265 Close csr_get_add_user_key;
266
267
268 Else
269
270 select to_char(sysdate,'J')||to_char(sysdate,'HH24MISS')||dbms_utility.get_hash_value(p_last_name||p_sex||p_first_name,0,1000)
271 into l_temp
272 from dual;
273
274 l_person_user_key := 'RI~PER~'||l_temp;
275 l_assignment_user_key := 'RI~ASG~'||l_temp;
276 l_address_user_key := 'RI~ADD~'||l_temp;
277
278 End If;
279
280 If l_dp_batch_line_id_emp Is Null Or get_line_status('HRDPV_CREATE_EMPLOYEE',l_dp_batch_line_id_emp) <> 'C' Then
281
282 -- Added because for all legislations which only have an international address style
283 -- donot have the legislation code same as the context code of the address descr. flex
284 -- and hence all those legislations have to be covered one after another here in this if
285 -- condition and also taken care of in the PerAdiEngine.java file
286 if p_per_information_category = 'AU_GLB' then
287 l_per_information_category := 'AU';
288 l_person_user_key := 'EMP~'||p_first_name || '~' || p_last_name || '~' || p_hire_date;
289 l_assignment_user_key := 'ASG~'||p_first_name || '~' || p_last_name || '~' || p_hire_date;
290 l_address_user_key := 'ADD~'||p_first_name || '~' || p_last_name || '~' || p_hire_date;
291 else
292 l_per_information_category := p_per_information_category;
293 end if;
294
295
296 hrdpp_create_employee.insert_batch_lines( p_batch_id => p_batch_id
297 ,p_data_pump_batch_line_id => l_dp_batch_line_id_emp
298 ,p_user_sequence => 1
299 ,p_link_value => p_link_value
300 ,p_hire_date => p_hire_date
301 ,p_last_name => p_last_name
302 ,p_sex => p_sex
303 ,p_per_comments => p_per_comments
304 ,p_date_employee_data_verified => p_date_employee_data_verified
305 ,p_date_of_birth => p_date_of_birth
306 ,p_email_address => p_email_address
307 ,p_employee_number => p_employee_number
308 ,p_expense_check_send_to_addres => p_expense_check_send_to_addres
309 ,p_first_name => p_first_name
310 ,p_known_as => p_known_as
311 ,p_marital_status => p_marital_status
312 ,p_middle_names => p_middle_names
313 ,p_nationality => p_nationality
317 ,p_title => p_title
314 ,p_national_identifier => p_national_identifier
315 ,p_previous_last_name => p_previous_last_name
316 ,p_registered_disabled_flag => p_registered_disabled_flag
318 ,p_work_telephone => p_work_telephone
319 ,p_attribute_category => p_attribute_category
320 ,p_attribute1 => p_attribute1
321 ,p_attribute2 => p_attribute2
322 ,p_attribute3 => p_attribute3
323 ,p_attribute4 => p_attribute4
324 ,p_attribute5 => p_attribute5
325 ,p_attribute6 => p_attribute6
326 ,p_attribute7 => p_attribute7
327 ,p_attribute8 => p_attribute8
328 ,p_attribute9 => p_attribute9
329 ,p_attribute10 => p_attribute10
330 ,p_attribute11 => p_attribute11
331 ,p_attribute12 => p_attribute12
332 ,p_attribute13 => p_attribute13
333 ,p_attribute14 => p_attribute14
334 ,p_attribute15 => p_attribute15
335 ,p_attribute16 => p_attribute16
336 ,p_attribute17 => p_attribute17
337 ,p_attribute18 => p_attribute18
338 ,p_attribute19 => p_attribute19
339 ,p_attribute20 => p_attribute20
340 ,p_attribute21 => p_attribute21
341 ,p_attribute22 => p_attribute22
342 ,p_attribute23 => p_attribute23
343 ,p_attribute24 => p_attribute24
344 ,p_attribute25 => p_attribute25
345 ,p_attribute26 => p_attribute26
346 ,p_attribute27 => p_attribute27
347 ,p_attribute28 => p_attribute28
348 ,p_attribute29 => p_attribute29
349 ,p_attribute30 => p_attribute30
350 ,p_per_information_category => l_per_information_category
351 ,p_per_information1 => p_per_information1
352 ,p_per_information2 => p_per_information2
353 ,p_per_information3 => p_per_information3
354 ,p_per_information4 => p_per_information4
355 ,p_per_information5 => p_per_information5
356 ,p_per_information6 => p_per_information6
357 ,p_per_information7 => p_per_information7
358 ,p_per_information8 => p_per_information8
359 ,p_per_information9 => p_per_information9
360 ,p_per_information10 => p_per_information10
361 ,p_per_information11 => p_per_information11
362 ,p_per_information12 => p_per_information12
363 ,p_per_information13 => p_per_information13
364 ,p_per_information14 => p_per_information14
365 ,p_per_information15 => p_per_information15
366 ,p_per_information16 => p_per_information16
367 ,p_per_information17 => p_per_information17
368 ,p_per_information18 => p_per_information18
369 ,p_per_information19 => p_per_information19
370 ,p_per_information20 => p_per_information20
371 ,p_per_information21 => p_per_information21
372 ,p_per_information22 => p_per_information22
373 ,p_per_information23 => p_per_information23
377 ,p_per_information27 => p_per_information27
374 ,p_per_information24 => p_per_information24
375 ,p_per_information25 => p_per_information25
376 ,p_per_information26 => p_per_information26
378 ,p_per_information28 => p_per_information28
379 ,p_per_information29 => p_per_information29
380 ,p_per_information30 => p_per_information30
381 ,p_date_of_death => p_date_of_death
382 ,p_background_check_status => p_background_check_status
383 ,p_background_date_check => p_background_date_check
384 ,p_blood_type => p_blood_type
385 ,p_fast_path_employee => p_fast_path_employee
386 ,p_fte_capacity => p_fte_capacity
387 ,p_honors => p_honors
388 ,p_internal_location => p_internal_location
389 ,p_last_medical_test_by => p_last_medical_test_by
390 ,p_last_medical_test_date => p_last_medical_test_date
391 ,p_mailstop => p_mailstop
392 ,p_office_number => p_office_number
393 ,p_on_military_service => p_on_military_service
394 ,p_pre_name_adjunct => p_pre_name_adjunct
395 ,p_projected_start_date => p_projected_start_date
396 ,p_resume_exists => p_resume_exists
397 ,p_resume_last_updated => p_resume_last_updated
398 ,p_second_passport_exists => null
399 ,p_student_status => null
400 ,p_work_schedule => null
401 ,p_suffix => p_suffix
402 ,p_receipt_of_death_cert_date => p_receipt_of_death_cert_date
403 ,p_coord_ben_med_pln_no => p_coord_ben_med_pln_no
404 ,p_coord_ben_no_cvg_flag => null
405 ,p_coord_ben_med_ext_er => p_coord_ben_med_ext_er
406 ,p_coord_ben_med_pl_name => p_coord_ben_med_pl_name
407 ,p_coord_ben_med_insr_crr_name => p_coord_ben_med_insr_crr_name
408 ,p_coord_ben_med_insr_crr_ident => p_coord_ben_med_insr_crr_ident
409 ,p_coord_ben_med_cvg_strt_dt => p_coord_ben_med_cvg_strt_dt
410 ,p_coord_ben_med_cvg_end_dt => p_coord_ben_med_cvg_end_dt
411 ,p_uses_tobacco_flag => p_uses_tobacco_flag
412 ,p_dpdnt_adoption_date => p_dpdnt_adoption_date
413 ,p_dpdnt_vlntry_svce_flag => p_dpdnt_vlntry_svce_flag
414 ,p_original_date_of_hire => p_original_date_of_hire
415 ,p_adjusted_svc_date => p_adjusted_svc_date
416 ,p_town_of_birth => p_town_of_birth
417 ,p_region_of_birth => p_region_of_birth
418 ,p_country_of_birth => p_country_of_birth
419 ,p_global_person_id => p_global_person_id
420 ,p_party_id => p_party_id
421 ,p_person_user_key => l_person_user_key
422 ,p_assignment_user_key => l_assignment_user_key
423 ,p_user_person_type => p_user_person_type
424 ,p_language_code => p_language_code
425 ,p_vendor_name => p_vendor_name
426 ,p_correspondence_language => p_correspondence_language
427 ,p_benefit_group => p_benefit_group
428 );
429
430
431 End If;
432
433
434 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
435
436 hrdpp_create_person_address.insert_batch_lines( p_batch_id => p_batch_id
437 ,p_data_pump_batch_line_id => l_dp_batch_line_id_add
438 ,p_user_sequence => 2
442 ,p_validate_county => p_validate_county
439 ,p_link_value => p_link_value
440 ,p_effective_date => p_hire_date
441 ,p_pradd_ovlapval_override => p_pradd_ovlapval_override
443 ,p_primary_flag => p_primary_flag
444 ,p_style => p_style
445 ,p_date_from => p_hire_date
446 ,p_date_to => p_date_to
447 ,p_address_type => p_address_type
448 ,p_comments => p_comments
449 ,p_address_line1 => p_address_line1
450 ,p_address_line2 => p_address_line2
451 ,p_address_line3 => p_address_line3
452 ,p_town_or_city => p_town_or_city
453 ,p_region_1 => p_region_1
454 ,p_region_2 => p_region_2
455 ,p_region_3 => p_region_3
456 ,p_postal_code => p_postal_code
457 ,p_telephone_number_1 => p_telephone_number_1
458 ,p_telephone_number_2 => p_telephone_number_2
459 ,p_telephone_number_3 => p_telephone_number_3
460 ,p_addr_attribute_category => p_addr_attribute_category
461 ,p_addr_attribute1 => p_addr_attribute1
462 ,p_addr_attribute2 => p_addr_attribute2
463 ,p_addr_attribute3 => p_addr_attribute3
464 ,p_addr_attribute4 => p_addr_attribute4
465 ,p_addr_attribute5 => p_addr_attribute5
466 ,p_addr_attribute6 => p_addr_attribute6
467 ,p_addr_attribute7 => p_addr_attribute7
468 ,p_addr_attribute8 => p_addr_attribute8
469 ,p_addr_attribute9 => p_addr_attribute9
470 ,p_addr_attribute10 => p_addr_attribute10
471 ,p_addr_attribute11 => p_addr_attribute11
472 ,p_addr_attribute12 => p_addr_attribute12
473 ,p_addr_attribute13 => p_addr_attribute13
474 ,p_addr_attribute14 => p_addr_attribute14
475 ,p_addr_attribute15 => p_addr_attribute15
476 ,p_addr_attribute16 => p_addr_attribute16
477 ,p_addr_attribute17 => p_addr_attribute17
478 ,p_addr_attribute18 => p_addr_attribute18
479 ,p_addr_attribute19 => p_addr_attribute19
480 ,p_addr_attribute20 => p_addr_attribute20
481 ,p_add_information13 => p_add_information13
482 ,p_add_information14 => p_add_information14
483 ,p_add_information15 => p_add_information15
484 ,p_add_information16 => p_add_information16
485 ,p_add_information17 => p_add_information17
486 ,p_add_information18 => p_add_information18
487 ,p_add_information19 => p_add_information19
488 ,p_add_information20 => p_add_information20
489 ,p_party_id => p_party_id
490 ,p_address_user_key => l_address_user_key
491 ,p_person_user_key => l_person_user_key
492 ,p_country => p_country
493 );
494 End If;
495
496 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
497
498 hrdpp_update_emp_asg.insert_batch_lines(p_batch_id => p_batch_id
499 ,p_data_pump_batch_line_id => l_dp_batch_line_id_asg
500 ,p_user_sequence => 3
501 ,p_effective_date => p_hire_date
505 ,p_con_seg_user_name => null
502 ,p_datetrack_update_mode => 'CORRECTION'
503 ,p_cagr_grade_def_id => null
504 ,p_assignment_user_key => l_assignment_user_key
506 ,p_segment1 => p_gre
507 );
508 End If;
509
510
511 If (p_payroll_name Is Not Null or p_asg_location Is Not Null Or p_pay_basis Is Not Null)
512 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
513
514 hrdpp_update_emp_asg_criteria.insert_batch_lines(p_batch_id => p_batch_id
515 ,p_data_pump_batch_line_id => l_dp_batch_line_id_asg_cri
516 ,p_user_sequence => 4
517 ,p_effective_date => p_hire_date
518 ,p_datetrack_update_mode => 'CORRECTION'
519 ,p_special_ceiling_step_id => null
520 ,p_people_group_id => null
521 ,p_assignment_user_key => l_assignment_user_key
522 ,p_payroll_name => p_payroll_name
523 ,p_location_code => p_asg_location
524 ,p_language_code => userenv('lang')
525 ,p_pay_basis_name => p_pay_basis
526 ,p_con_seg_user_name => null
527 );
528
529 End If;
530
531
532 If (p_proposed_salary Is Not Null or p_change_date Is Not Null Or p_proposal_reason Is Not Null )
533 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
534
535 hrdpp_upload_salary_proposal.insert_batch_lines(p_batch_id => p_batch_id
536 ,p_data_pump_batch_line_id => l_dp_batch_line_id_sal
537 ,p_user_sequence => 5
538 ,p_change_date => p_change_date
539 ,p_proposed_salary => p_proposed_salary
540 ,p_proposal_reason => p_proposal_reason
541 ,p_pay_proposal_id => null
542 ,p_object_version_number => null
543 ,p_component_reason_1 => null
544 ,p_approved_1 => null
545 ,p_component_id_1 => null
546 ,p_ppc_object_version_number_1 => null
547 ,p_component_reason_2 => null
548 ,p_approved_2 => null
549 ,p_component_id_2 => null
550 ,p_ppc_object_version_number_2 => null
551 ,p_component_reason_3 => null
552 ,p_approved_3 => null
553 ,p_component_id_3 => null
554 ,p_ppc_object_version_number_3 => null
555 ,p_component_reason_4 => null
556 ,p_approved_4 => null
557 ,p_component_id_4 => null
558 ,p_ppc_object_version_number_4 => null
559 ,p_component_reason_5 => null
560 ,p_approved_5 => null
561 ,p_component_id_5 => null
562 ,p_ppc_object_version_number_5 => null
563 ,p_component_reason_6 => null
567 ,p_component_reason_7 => null
564 ,p_approved_6 => null
565 ,p_component_id_6 => null
566 ,p_ppc_object_version_number_6 => null
568 ,p_approved_7 => null
569 ,p_component_id_7 => null
570 ,p_ppc_object_version_number_7 => null
571 ,p_component_reason_8 => null
572 ,p_approved_8 => null
573 ,p_component_id_8 => null
574 ,p_ppc_object_version_number_8 => null
575 ,p_component_reason_9 => null
576 ,p_approved_9 => null
577 ,p_component_id_9 => null
578 ,p_ppc_object_version_number_9 => null
579 ,p_component_reason_10 => null
580 ,p_approved_10 => null
581 ,p_component_id_10 => null
582 ,p_ppc_object_version_number_10 => null
583 ,p_assignment_user_key => l_assignment_user_key
584 );
585 End If;
586
587
588 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
589
590 hrdpp_create_user_acct.insert_batch_lines(p_batch_id => p_batch_id
591 ,p_data_pump_batch_line_id => l_dp_batch_line_id_acc
592 ,p_per_effective_start_date => p_hire_date
593 ,p_user_sequence => 6
594 ,p_hire_date => p_hire_date
595 ,p_date_from => p_hire_date
596 ,p_person_user_key => l_person_user_key
597 );
598
599 End If;
600
601 End insert_batch_lines;
602
603 Procedure set_user_acct_details(
604 p_person_id In Number
605 ,p_per_effective_start_date In Date
606 ,p_per_effective_end_date In Date
607 ,p_assignment_id In Number
608 ,p_asg_effective_start_date In Date
609 ,p_asg_effective_end_date In Date
610 ,p_business_group_id In Number
611 ,p_date_from In Date
612 ,p_date_to In Date
613 ,p_org_structure_id In Number
614 ,p_org_structure_vers_id In Number
615 ,p_parent_org_id In Number
616 ,p_single_org_id In Number
617 ,p_run_type In Varchar2
618 ,p_hire_date In Date) Is
619
620 Cursor csr_emp_details Is
621 Select emp.last_name
622 ,emp.first_name
623 ,emp.full_name
624 ,emp.email_address
625 From per_people_f emp
626 Where emp.person_id = p_person_id
627 and emp.effective_start_date = p_per_effective_start_date
628 and emp.effective_end_date = nvl(p_per_effective_end_date,emp.effective_end_date);
629
630 Cursor csr_responsibility_details Is
631 Select res.application_id
632 ,res.responsibility_id
633 From fnd_responsibility res
634 Where res.responsibility_key = 'EMPLOYEE_DIRECT_ACCESS_V4.0';
635
636 l_last_name Varchar2(150);
637 l_first_name Varchar2(150);
638 l_full_name Varchar2(240);
639 l_email_address Varchar2(240);
640
641 l_application_id Number;
642 l_responsibility_id Number;
643
644 Begin
645
646 Open csr_emp_details;
647 Fetch csr_emp_details Into l_last_name, l_first_name, l_full_name, l_email_address;
648 Close csr_emp_details;
649
650 hr_user_acct_utility.g_fnd_user_rec.user_name := l_first_name||'.'||l_last_name;
651 hr_user_acct_utility.g_fnd_user_rec.password := 'Welcome';
652 hr_user_acct_utility.g_fnd_user_rec.email_address := l_email_address;
653 hr_user_acct_utility.g_fnd_user_rec.password_date := p_hire_date;
654 hr_user_acct_utility.g_fnd_user_rec.employee_id := p_person_id;
655
656 Open csr_responsibility_details;
657 Fetch csr_responsibility_details Into l_application_id, l_responsibility_id;
658 Close csr_responsibility_details;
659
660 hr_user_acct_utility.g_fnd_resp_tbl(1).existing_resp_id := l_responsibility_id;
661 hr_user_acct_utility.g_fnd_resp_tbl(1).existing_resp_key := 'EMPLOYEE_DIRECT_ACCESS_V4.0';
662 hr_user_acct_utility.g_fnd_resp_tbl(1).existing_resp_app_id := l_application_id;
663 hr_user_acct_utility.g_fnd_resp_tbl(1).sec_group_id := 0;
664 hr_user_acct_utility.g_fnd_resp_tbl(1).sec_profile_id := 0;
665 hr_user_acct_utility.g_fnd_resp_tbl(1).user_resp_start_date := p_hire_date;
666
667 End set_user_acct_details;
668
669 End per_ri_create_crp_employee;