[Home] [Help]
PACKAGE BODY: APPS.POR_LOAD_EMPLOYEE
Source
1 PACKAGE BODY POR_LOAD_EMPLOYEE as
2 /* $Header: PORLEMPB.pls 115.9 2004/02/05 22:40:57 skaushik ship $ */
3
4 PROCEDURE insert_update_employee_info (
5 x_employee_number IN VARCHAR2,
6 x_first_name IN VARCHAR2,
7 x_last_name IN VARCHAR2,
8 x_sex IN VARCHAR2,
9 x_effective_start_date IN DATE,
10 x_effective_end_date IN DATE,
11 x_business_group_name IN VARCHAR2,
12 x_location_name IN VARCHAR2,
13 x_default_employee_account IN VARCHAR2,
14 x_set_of_books_name IN VARCHAR2,
15 x_supervisor_emp_number IN VARCHAR2,
16 x_job_name IN VARCHAR2,
17 x_email_address IN VARCHAR2,
18 x_work_telephone IN VARCHAR2)
19 IS
20
21 l_person_id NUMBER;
22 l_assignment_id NUMBER;
23 l_per_object_version_number NUMBER;
24 l_asg_object_version_number NUMBER;
25 l_full_name VARCHAR2(30);
26 l_per_comment_id NUMBER;
30 l_effective_end_date DATE;
27 l_assignment_sequence NUMBER;
28 l_effective_start_date DATE;
29 effective_end_date DATE;
31 l_per_effective_end_date DATE;
32 l_assignment_number VARCHAR2(30);
33 l_name_combination_warning BOOLEAN;
34 l_assign_payroll_warning BOOLEAN;
35 l_business_group_id NUMBER;
36 l_employee_number VARCHAR2(30);
37 l_set_of_books_id NUMBER;
38 l_group_name VARCHAR2(20);
39 l_concatenated_segments VARCHAR2(20);
40 l_cagr_grade_def_id NUMBER;
41 l_cagr_concatenated_segments VARCHAR2(20);
42 l_soft_coding_keyflex_id NUMBER;
43 l_people_group_id NUMBER;
44 l_other_manager_warning BOOLEAN;
45 l_chart_of_accounts_id NUMBER;
46 l_ccid NUMBER;
47 l_row_id VARCHAR2(30);
48 p_Effective_Start_Date DATE;
49 p_Effective_End_Date DATE;
50 p_Business_Group_Id NUMBER;
51 p_Person_Type_Id NUMBER;
52 p_Last_Name PER_ALL_PEOPLE_F.LAST_NAME%TYPE;
53 p_Start_Date DATE;
54 p_Hire_date DATE;
55 p_S_Hire_Date DATE;
56 p_Period_of_service_id NUMBER;
57 p_Termination_Date DATE;
58 p_S_Termination_Date DATE;
59 p_Applicant_Number VARCHAR2(30);
60 p_Comment_Id NUMBER;
61 p_Current_Applicant_Flag VARCHAR2(30);
62 p_Current_Emp_Or_Apl_Flag VARCHAR2(30);
63 p_Current_Employee_Flag VARCHAR2(30);
64 p_Date_Employee_Data_Verified DATE;
65 p_Date_Of_Birth DATE;
66 p_Email_Address VARCHAR2(240);
67 p_Expense_Check_To_Address VARCHAR2(30);
68 p_First_Name PER_ALL_PEOPLE_F.FIRST_NAME%TYPE;
69 p_Full_Name VARCHAR2(240);
70 p_Known_As VARCHAR2(30);
71 p_Marital_Status VARCHAR2(30);
72 p_Middle_Names VARCHAR2(30);
73 p_Nationality VARCHAR2(30);
74 p_National_Identifier VARCHAR2(30);
75 p_Previous_Last_Name PER_ALL_PEOPLE_F.PREVIOUS_LAST_NAME%TYPE;
76 p_Registered_Disabled_Flag VARCHAR2(30);
77 p_Sex VARCHAR2(30);
78 p_Title VARCHAR2(30);
79 p_Vendor_Id NUMBER(30);
80 p_Work_Telephone VARCHAR2(60);
81 p_Attribute_Category VARCHAR2(30);
82 p_Attribute1 VARCHAR2(150);
83 p_Attribute2 VARCHAR2(150);
84 p_Attribute3 VARCHAR2(150);
85 p_Attribute4 VARCHAR2(150);
86 p_Attribute5 VARCHAR2(150);
87 p_Attribute6 VARCHAR2(150);
88 p_Attribute7 VARCHAR2(150);
89 p_Attribute8 VARCHAR2(150);
90 p_Attribute9 VARCHAR2(150);
91 p_Attribute10 VARCHAR2(150);
92 p_Attribute11 VARCHAR2(150);
93 p_Attribute12 VARCHAR2(150);
94 p_Attribute13 VARCHAR2(150);
95 p_Attribute14 VARCHAR2(150);
96 p_Attribute15 VARCHAR2(150);
97 p_Attribute16 VARCHAR2(150);
98 p_Attribute17 VARCHAR2(150);
99 p_Attribute18 VARCHAR2(150);
100 p_Attribute19 VARCHAR2(150);
101 p_Attribute20 VARCHAR2(150);
102 p_Attribute21 VARCHAR2(150);
103 p_Attribute22 VARCHAR2(150);
104 p_Attribute23 VARCHAR2(150);
105 p_Attribute24 VARCHAR2(150);
106 p_Attribute25 VARCHAR2(150);
107 p_Attribute26 VARCHAR2(150);
108 p_Attribute27 VARCHAR2(150);
109 p_Attribute28 VARCHAR2(150);
110 p_Attribute29 VARCHAR2(150);
111 p_Attribute30 VARCHAR2(150);
112 p_Last_Update_Date DATE;
113 p_Last_Updated_By NUMBER;
114 p_Last_Update_Login NUMBER;
115
116 BEGIN
117
118
119 l_business_group_id := get_business_group_id(x_business_group_name);
120
121 l_employee_number := x_employee_number;
122 l_effective_start_date := x_effective_start_date;
123
124 l_effective_end_date := x_effective_end_date;
125
126 l_person_id := get_employee_exists (x_employee_number);
127
128
129 IF (l_person_id IS NULL) THEN
130
131 hr_employee_api.create_us_employee(
132 p_validate => FALSE
133 ,p_hire_date => x_effective_start_date
134 ,p_business_group_id => l_business_group_id
135 ,p_last_name => x_last_name
136 ,p_first_name => x_first_name
137 ,p_email_address => x_email_address
138 ,p_middle_names => NULL
139 ,p_sex => x_sex
140 ,p_work_telephone => x_work_telephone
141 ,p_employee_number => l_employee_number
142 ,p_person_id => l_person_id
143 ,p_assignment_id => l_assignment_id
144 ,p_per_object_version_number => l_per_object_version_number
145 ,p_asg_object_version_number => l_asg_object_version_number
146 ,p_per_effective_start_date => l_effective_start_date
147 ,p_per_effective_end_date => l_per_effective_end_date
148 ,p_full_name => l_full_name
149 ,p_per_comment_id => l_per_comment_id
150 ,p_assignment_sequence => l_assignment_sequence
151 ,p_assignment_number => l_assignment_number
152 ,p_name_combination_warning => l_name_combination_warning
153 ,p_assign_payroll_warning => l_assign_payroll_warning
154 );
155
156
157 ELSE
158
159 l_row_id := get_row_id(x_employee_number);
160
161 get_employee_details(x_employee_number,
162 p_Effective_Start_Date,
163 p_Effective_End_Date,
164 p_Business_Group_Id,
165 p_Person_Type_Id,
166 p_Last_Name,
167 p_Start_Date,
168 p_Hire_date,
169 p_Applicant_Number,
170 p_Comment_Id,
171 p_Current_Applicant_Flag,
172 p_Current_Emp_Or_Apl_Flag,
173 p_Current_Employee_Flag,
174 p_Date_Employee_Data_Verified,
175 p_Date_Of_Birth,
179 p_Full_Name,
176 p_Email_Address,
177 p_Expense_Check_To_Address,
178 p_First_Name,
180 p_Known_As,
181 p_Marital_Status,
182 p_Middle_Names,
183 p_Nationality,
184 p_National_Identifier,
185 p_Previous_Last_Name,
186 p_Registered_Disabled_Flag,
187 p_Sex,
188 p_Title,
189 p_Vendor_Id,
190 p_Work_Telephone,
191 p_Attribute_Category,
192 p_Attribute1,
193 p_Attribute2,
194 p_Attribute3,
195 p_Attribute4,
196 p_Attribute5,
197 p_Attribute6,
198 p_Attribute7,
199 p_Attribute8,
200 p_Attribute9,
201 p_Attribute10,
202 p_Attribute11,
203 p_Attribute12,
204 p_Attribute13,
205 p_Attribute14,
206 p_Attribute15,
207 p_Attribute16,
208 p_Attribute17,
209 p_Attribute18,
210 p_Attribute19,
211 p_Attribute20,
212 p_Attribute21,
213 p_Attribute22,
214 p_Attribute23,
215 p_Attribute24,
216 p_Attribute25,
217 p_Attribute26,
218 p_Attribute27,
219 p_Attribute28,
220 p_Attribute29,
221 p_Attribute30,
222 p_Last_Update_Date,
223 p_Last_Updated_By,
224 p_Last_Update_Login);
225
226
227 IF (l_effective_end_date IS NULL) THEN
228 effective_end_date := p_Effective_End_Date;
229 ELSE
230 effective_end_date := l_effective_end_date;
231 END IF;
232
233 PER_PEOPLE_V15_PKG.Update_Row(
234 X_Rowid => l_row_id,
235 X_Person_Id => l_person_id,
236 X_Effective_Start_Date => l_effective_start_date,
237 X_Effective_End_Date => effective_end_date,
238 X_Business_Group_Id => l_business_group_id,
239 X_Person_Type_Id => p_Person_Type_Id,
240 X_Last_Name => x_last_name,
241 X_Email_Address => x_email_address,
242 X_Start_Date => p_Start_Date,
243 X_Hire_date => p_Hire_date,
244 X_S_Hire_Date => NULL,
245 X_Period_of_service_id => NULL,
246 X_Termination_Date => NULL,
247 X_S_Termination_Date => NULL,
248 X_Applicant_Number => p_Applicant_Number,
249 X_Comment_Id => p_Comment_Id,
250 X_Current_Applicant_Flag => p_Current_Applicant_Flag,
251 X_Current_Emp_Or_Apl_Flag => p_Current_Emp_Or_Apl_Flag,
252 X_Current_Employee_Flag => p_Current_Employee_Flag,
253 X_Date_Employee_Data_Verified => p_Date_Employee_Data_Verified,
254 X_Date_Of_Birth => p_Date_Of_Birth,
255 X_Employee_Number => x_employee_number,
256 X_Expense_Check_To_Address => p_Expense_Check_To_Address,
257 X_First_Name => x_first_name,
258 X_Full_Name => p_Full_Name,
259 X_Known_As => p_Known_As,
260 X_Marital_Status =>p_Marital_Status ,
261 X_Middle_Names => p_Middle_Names,
262 X_Nationality => p_Nationality,
263 X_National_Identifier => p_National_Identifier,
264 X_Previous_Last_Name => p_Previous_Last_Name,
265 X_Registered_Disabled_Flag => p_Registered_Disabled_Flag,
266 X_Sex => x_sex,
267 X_Title => p_Title,
268 X_Vendor_Id => p_Vendor_Id,
269 X_Work_Telephone => x_work_telephone,
270 X_Attribute_Category => p_Attribute_Category,
271 X_Attribute1 => p_Attribute1,
272 X_Attribute2 => p_Attribute2,
273 X_Attribute3 => p_Attribute3,
274 X_Attribute4 => p_Attribute4,
275 X_Attribute5 => p_Attribute5,
276 X_Attribute6 => p_Attribute6,
277 X_Attribute7 => p_Attribute7,
278 X_Attribute8 => p_Attribute8,
279 X_Attribute9 => p_Attribute9,
280 X_Attribute10 => p_Attribute10,
281 X_Attribute11 => p_Attribute11,
282 X_Attribute12 => p_Attribute12,
283 X_Attribute13 => p_Attribute13,
284 X_Attribute14 => p_Attribute14,
285 X_Attribute15 => p_Attribute15,
286 X_Attribute16 => p_Attribute16,
287 X_Attribute17 => p_Attribute17,
288 X_Attribute18 => p_Attribute18,
289 X_Attribute19 => p_Attribute19,
290 X_Attribute20 => p_Attribute20,
291 X_Attribute21 => p_Attribute21,
292 X_Attribute22 => p_Attribute22,
296 X_Attribute26 => p_Attribute26,
293 X_Attribute23 => p_Attribute23,
294 X_Attribute24 => p_Attribute24,
295 X_Attribute25 => p_Attribute25,
297 X_Attribute27 => p_Attribute27,
298 X_Attribute28 => p_Attribute28,
299 X_Attribute29 => p_Attribute29,
300 X_Attribute30 => p_Attribute30,
301 X_Last_Update_Date => sysdate,
302 X_Last_Updated_By => p_Last_Updated_By,
303 X_Last_Update_Login => p_Last_Update_Login);
304
305 END IF;
306
307 POR_LOAD_EMPLOYEE_ASSIGNMENT.insert_update_employee_asg(
308 l_person_id,
309 l_business_group_id,
310 x_location_name,
311 l_assignment_number,
312 x_default_employee_account,
313 x_set_of_books_name,
314 x_job_name,
315 x_supervisor_emp_number,
316 x_effective_start_date,
317 x_effective_end_date);
318
319 EXCEPTION
320
321 WHEN NO_DATA_FOUND THEN
322 RETURN;
323
324 WHEN OTHERS THEN
325
326 RAISE;
327
328 END insert_update_employee_info;
329
330
331 FUNCTION get_business_group_id (p_business_group_name IN VARCHAR2) RETURN NUMBER IS
332 l_business_group_id NUMBER;
333 BEGIN
334
335 SELECT business_group_id INTO l_business_group_id
336 FROM per_business_groups
337 WHERE name = p_business_group_name;
338
339 RETURN l_business_group_id;
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 RETURN NULL;
343
344 END get_business_group_id;
345
346
347 FUNCTION get_employee_exists (p_employee_number IN VARCHAR2) RETURN NUMBER IS
348 l_person_id NUMBER;
349
350 BEGIN
351
352 SELECT person_id INTO l_person_id
353 FROM per_all_people_f
354 WHERE employee_number = p_employee_number;
355
356 RETURN l_person_id;
357
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 RETURN NULL;
361
362 END get_employee_exists;
363
364 FUNCTION get_row_id (p_employee_number IN VARCHAR2) RETURN VARCHAR2 IS
365 l_row_id VARCHAR2(30);
366
367 BEGIN
368
369 SELECT rowid INTO l_row_id
370 FROM per_all_people_f
371 WHERE employee_number = p_employee_number;
372
373 RETURN l_row_id;
374
375 EXCEPTION
376 WHEN NO_DATA_FOUND THEN
377 RETURN NULL;
378
379 END get_row_id;
380
381 PROCEDURE get_employee_details(x_employee_number IN VARCHAR2,
382 p_Effective_Start_Date OUT NOCOPY DATE,
383 p_Effective_End_Date OUT NOCOPY DATE,
384 p_Business_Group_Id OUT NOCOPY NUMBER,
385 p_Person_Type_Id OUT NOCOPY NUMBER,
386 p_Last_Name OUT NOCOPY VARCHAR2,
387 p_Start_Date OUT NOCOPY DATE,
388 p_Hire_date OUT NOCOPY DATE,
389 p_Applicant_Number OUT NOCOPY VARCHAR2,
390 p_Comment_Id OUT NOCOPY NUMBER,
391 p_Current_Applicant_Flag OUT NOCOPY VARCHAR2,
392 p_Current_Emp_Or_Apl_Flag OUT NOCOPY VARCHAR2,
393 p_Current_Employee_Flag OUT NOCOPY VARCHAR2,
394 p_Date_Employee_Data_Verified OUT NOCOPY DATE,
395 p_Date_Of_Birth OUT NOCOPY DATE,
396 p_Email_Address OUT NOCOPY VARCHAR2,
397 p_Expense_Check_To_Address OUT NOCOPY VARCHAR2,
398 p_First_Name OUT NOCOPY VARCHAR2,
399 p_Full_Name OUT NOCOPY VARCHAR2,
400 p_Known_As OUT NOCOPY VARCHAR2,
401 p_Marital_Status OUT NOCOPY VARCHAR2,
402 p_Middle_Names OUT NOCOPY VARCHAR2,
403 p_Nationality OUT NOCOPY VARCHAR2,
404 p_National_Identifier OUT NOCOPY VARCHAR2,
405 p_Previous_Last_Name OUT NOCOPY VARCHAR2,
406 p_Registered_Disabled_Flag OUT NOCOPY VARCHAR2,
407 p_Sex OUT NOCOPY VARCHAR2,
408 p_Title OUT NOCOPY VARCHAR2,
409 p_Vendor_Id OUT NOCOPY NUMBER,
410 p_Work_Telephone OUT NOCOPY VARCHAR2,
411 p_Attribute_Category OUT NOCOPY VARCHAR2,
412 p_Attribute1 OUT NOCOPY VARCHAR2,
413 p_Attribute2 OUT NOCOPY VARCHAR2,
414 p_Attribute3 OUT NOCOPY VARCHAR2,
415 p_Attribute4 OUT NOCOPY VARCHAR2,
416 p_Attribute5 OUT NOCOPY VARCHAR2,
417 p_Attribute6 OUT NOCOPY VARCHAR2,
418 p_Attribute7 OUT NOCOPY VARCHAR2,
419 p_Attribute8 OUT NOCOPY VARCHAR2,
420 p_Attribute9 OUT NOCOPY VARCHAR2,
421 p_Attribute10 OUT NOCOPY VARCHAR2,
422 p_Attribute11 OUT NOCOPY VARCHAR2,
423 p_Attribute12 OUT NOCOPY VARCHAR2,
424 p_Attribute13 OUT NOCOPY VARCHAR2,
425 p_Attribute14 OUT NOCOPY VARCHAR2,
426 p_Attribute15 OUT NOCOPY VARCHAR2,
427 p_Attribute16 OUT NOCOPY VARCHAR2,
428 p_Attribute17 OUT NOCOPY VARCHAR2,
429 p_Attribute18 OUT NOCOPY VARCHAR2,
430 p_Attribute19 OUT NOCOPY VARCHAR2,
431 p_Attribute20 OUT NOCOPY VARCHAR2,
435 p_Attribute24 OUT NOCOPY VARCHAR2,
432 p_Attribute21 OUT NOCOPY VARCHAR2,
433 p_Attribute22 OUT NOCOPY VARCHAR2,
434 p_Attribute23 OUT NOCOPY VARCHAR2,
436 p_Attribute25 OUT NOCOPY VARCHAR2,
437 p_Attribute26 OUT NOCOPY VARCHAR2,
438 p_Attribute27 OUT NOCOPY VARCHAR2,
439 p_Attribute28 OUT NOCOPY VARCHAR2,
440 p_Attribute29 OUT NOCOPY VARCHAR2,
441 p_Attribute30 OUT NOCOPY VARCHAR2,
442 p_Last_Update_Date OUT NOCOPY DATE,
443 p_Last_Updated_By OUT NOCOPY NUMBER,
444 p_Last_Update_Login OUT NOCOPY NUMBER)
445
446 IS
447 BEGIN
448
449 SELECT EFFECTIVE_START_DATE,
450 EFFECTIVE_END_DATE,
451 BUSINESS_GROUP_ID,
452 PERSON_TYPE_ID,
453 LAST_NAME,
454 START_DATE,
455 ORIGINAL_DATE_OF_HIRE,
456 APPLICANT_NUMBER,
457 COMMENT_ID,
458 CURRENT_APPLICANT_FLAG,
459 CURRENT_EMP_OR_APL_FLAG,
460 CURRENT_EMPLOYEE_FLAG,
461 DATE_EMPLOYEE_DATA_VERIFIED,
462 DATE_OF_BIRTH,
463 EMAIL_ADDRESS,
464 EXPENSE_CHECK_SEND_TO_ADDRESS,
465 FIRST_NAME,
466 FULL_NAME,
467 KNOWN_AS,
468 MARITAL_STATUS,
469 MIDDLE_NAMES,
470 NATIONALITY,
471 NATIONAL_IDENTIFIER,
472 PREVIOUS_LAST_NAME,
473 REGISTERED_DISABLED_FLAG,
474 SEX,
475 TITLE,
476 VENDOR_ID,
477 WORK_TELEPHONE,
478 ATTRIBUTE_CATEGORY,
479 ATTRIBUTE1,
480 ATTRIBUTE2,
481 ATTRIBUTE3,
482 ATTRIBUTE4,
483 ATTRIBUTE5,
484 ATTRIBUTE6,
485 ATTRIBUTE7,
486 ATTRIBUTE8,
487 ATTRIBUTE9,
488 ATTRIBUTE10,
489 ATTRIBUTE11,
490 ATTRIBUTE12,
491 ATTRIBUTE13,
492 ATTRIBUTE14,
493 ATTRIBUTE15,
494 ATTRIBUTE16,
495 ATTRIBUTE17,
496 ATTRIBUTE18,
497 ATTRIBUTE19,
498 ATTRIBUTE20,
499 ATTRIBUTE21,
500 ATTRIBUTE22,
501 ATTRIBUTE23,
502 ATTRIBUTE24,
503 ATTRIBUTE25,
504 ATTRIBUTE26,
505 ATTRIBUTE27,
506 ATTRIBUTE28,
507 ATTRIBUTE29,
508 ATTRIBUTE30,
509 LAST_UPDATE_DATE,
510 LAST_UPDATED_BY,
511 LAST_UPDATE_LOGIN
512
513 INTO p_Effective_Start_Date,
514 p_Effective_End_Date,
515 p_Business_Group_Id,
516 p_Person_Type_Id,
517 p_Last_Name,
518 p_Start_Date,
519 p_Hire_date,
520 p_Applicant_Number,
521 p_Comment_Id,
522 p_Current_Applicant_Flag,
523 p_Current_Emp_Or_Apl_Flag,
524 p_Current_Employee_Flag,
525 p_Date_Employee_Data_Verified,
526 p_Date_Of_Birth,
527 p_Email_Address,
528 p_Expense_Check_To_Address,
529 p_First_Name,
530 p_Full_Name,
531 p_Known_As,
532 p_Marital_Status,
533 p_Middle_Names,
534 p_Nationality,
535 p_National_Identifier,
536 p_Previous_Last_Name,
537 p_Registered_Disabled_Flag,
538 p_Sex,
539 p_Title,
540 p_Vendor_Id,
541 p_Work_Telephone,
542 p_Attribute_Category,
543 p_Attribute1,
544 p_Attribute2,
545 p_Attribute3,
546 p_Attribute4,
547 p_Attribute5,
548 p_Attribute6,
549 p_Attribute7,
550 p_Attribute8,
551 p_Attribute9,
552 p_Attribute10,
553 p_Attribute11,
554 p_Attribute12,
555 p_Attribute13,
556 p_Attribute14,
557 p_Attribute15,
558 p_Attribute16,
559 p_Attribute17,
560 p_Attribute18,
561 p_Attribute19,
562 p_Attribute20,
563 p_Attribute21,
564 p_Attribute22,
565 p_Attribute23,
566 p_Attribute24,
567 p_Attribute25,
568 p_Attribute26,
569 p_Attribute27,
570 p_Attribute28,
571 p_Attribute29,
572 p_Attribute30,
573 p_Last_Update_Date,
574 p_Last_Updated_By,
575 p_Last_Update_Login
576 FROM per_all_people_f
577 WHERE EMPLOYEE_NUMBER = x_employee_number;
578
579 END get_employee_details;
580
581 END POR_LOAD_EMPLOYEE;