CREATE OR REPLACE PACKAGE BODY XX_IMPORT_EMPLOYEES_NEW IS
PROCEDURE XX_create_employee IS
CURSOR Cur0 IS
SELECT MESSAGE,ROWID
FROM XX_PER_ALL_PEOPLE_F_INT
WHERE TO_CHAR(INTERFACE_DATE,’DD-MON-YY’) = TO_CHAR(SYSDATE,’DD-MON-YY’)
AND STATUS_FLAG = ‘N’;
CURSOR cx IS
Select EFFECTIVE_START_DATE,
PERSONTYPE,
TITLEDESC,
FIRST_NAME,
MIDDLE_NAME,
LAST_NAME,
father_name,
DOB,
GENDER,
MARITAL_STATUS,
NATIONAL_IDENTIFIER,
ADDRESS_LINE1,
ADDRESS_LINE2, TOWN_OR_CITY,
STATENAME,
POSTAL_CODE,
COUNTRYNAME,
HOMEPHONENUMBER,
— GRADENAME,
— JOBNAME,
— SALARYBASIS,
— SALARY,
STATUS_FLAG,
MESSAGE,
INTERFACE_DATE,
EMPLOYEE_No,
ROWID ,
SUPERVISOR_NO
FROM XX_PER_ALL_PEOPLE_F_INT
WHERE STATUS_FLAG IS NULL;
— local variable declaration begin
v_titledesc varchar2(10);
v_HOMEPHONENUMBER varchar2(30);
v_business_group_id NUMBER := 0;
v_person_type_id NUMBER := 3;
v_marital_status Varchar2(30);
v_primary_flag VARCHAR2(1) := ‘Y’;
v_validate_county BOOLEAN := FALSE;
v_validate BOOLEAN := FALSE;
v_country VARCHAR2(20);
v_national_identifier Varchar2(30);
v_hire_date Date;
v_email_address Varchar2(100);
v_address_line1 varchar2(100);
v_address_line2 varchar2(100);
— NEXT
v_ssn VARCHAR2(30) DEFAULT NULL;
Len_Message Number;
Number_Log Number;
A Number; — for temporary purpose
B Number; — for temporary purpose
Err_Flag Varchar2(10); — to store the each validation status
Err_Mess Varchar2(2000); — to store the error message
Rec_Count Number(10); — to store the value for a select statement
Count1 Number := 0; — to store the number of records inserted
Count2 Number := 0; — to store the number of records not inserted
Num Number := 0; — to store the count of records
v_comma_pos NUMBER;
v_mi_pos NUMBER;
v_pay_proposal_id NUMBER;
v_change_date DATE;
v_last_name VARCHAR2(255);
v_first_name VARCHAR2(255);
v_mi_name VARCHAR2(255);
v_pay_basis_id Number;
v_employee_number VARCHAR2(20);
v_person_id NUMBER;
v_proposed_salary_n NUMBER;
v_approved VARCHAR2(1) := ‘Y’;
v_per_object_version_number NUMBER;
v_asg_object_version_number NUMBER;
v_per_effective_start_date DATE;
v_per_effective_end_date DATE;
v_proposed_salary_warning BOOLEAN;
v_full_name VARCHAR2(100);
v_per_comment_id NUMBER;
v_assignment_sequence NUMBER;
v_assignment_number NUMBER;
v_name_combination_warning BOOLEAN;
v_assign_payroll_warning BOOLEAN;
v_orig_hire_warning BOOLEAN;
v_birth_date DATE;
v_approved_warning BOOLEAN;
v_payroll_warning BOOLEAN;
v_sqlcode VARCHAR2(15);
v_sqlerrm VARCHAR2(2000);
v_title_description VARCHAR2(10);
v_party_id NUMBER;
v_comma_pos NUMBER;
v_city VARCHAR2(30);
v_element_entry_id NUMBER DEFAULT NULL;
v_county VARCHAR2(30);
v_state VARCHAR2(30);
v_home_telno VARCHAR2(15);
v_address_id NUMBER;
v_effective_date DATE;
v_add_upd BOOLEAN DEFAULT FALSE;
v_date_from DATE;
v_date_to DATE;
v_datetrack_update_mode Varchar2(200):= ‘CORRECTION’;
v_assignment_id NUMBER;
v_grade_id NUMBER;
v_position_id NUMBER;
v_job_id NUMBER;
v_location_id NUMBER;
v_organization_id NUMBER;
v_object_version_number NUMBER ;
v_soft_coding_keyflex_id NUMBER;
v_comment_id NUMBER;
v_concatenated_segments VARCHAR2(2000);
v_no_managers_warning BOOLEAN;
v_effective_start_date DATE;
v_effective_end_date DATE;
v_inv_next_sal_date_warning BOOLEAN;
v_special_ceiling_step_id NUMBER ;
v_org_now_no_manager_warning BOOLEAN;
v_entries_changed_warning VARCHAR2(240);
v_tax_district_changed_wrng BOOLEAN;
v_people_group_id NUMBER;
v_group_name VARCHAR2(120);
v_tax_district_changed_wrng BOOLEAN;
v_tax_district_changed_warning boolean;
v_spp_delete_warning BOOLEAN;
v_other_manager_warning BOOLEAN;
v_block_name Varchar2(200);
v_nationality VARCHAR2(100);
v_father_name VARCHAR2(255);
v_assignment_status_type_id NUMBER;
— v_assignment_id NUMBER;
v_org_id NUMBER;
v_set_of_books_id NUMBER;
v_assignment_no VARCHAR2(30);
V_SUPERVISOR_ID NUMBER;
— v_effective_start_date DATE;
— v_effective_end_date DATE;
V_cagr_grade_def_id per_cagr_grades_def.cagr_grade_def_id%TYPE;
V_cagr_concatenated_segments VARCHAR2(2000);
— V_soft_coding_keyflex_id per_all_assignments_f.soft_coding_keyflex_id%TYPE;
–V_comment_id per_all_assignments_f.comment_id%TYPE;
–V_concatenated_segments hr_soft_coding_keyflex.concatenated_segments%TYPE;
— V_no_managers_warning BOOLEAN;
— V_other_manager_warning BOOLEAN;
— local variable declaration over
BEGIN
dbms_output.put_line(‘here’);
FOR i_rec IN cx
LOOP
dbms_output.put_line(‘here–‘||i_rec.employee_no);
v_employee_number := i_rec.employee_no;
v_titledesc := UPPER(i_rec.titledesc);
— v_national_identifier := i_rec.national_identifier;
v_last_name := NVL(LTRIM(RTRIM(INITCAP(i_rec.last_name))),NULL);
v_first_name := NVL(LTRIM(RTRIM(INITCAP(i_rec.first_name))),NULL);
v_mi_name := NVL(LTRIM(RTRIM(INITCAP(i_rec.first_name))),NULL);
–v_birth_date := Trunc(i_rec.DOB);
v_hire_date := trunc(i_rec.effective_start_date);
v_birth_date := trunc(i_rec.DOB);
v_business_group_id := 81;–FND_PROFILE.VALUE(‘PER_BUSINESS_GROUP_ID’);
— v_marital_status := upper(rtrim(ltrim(i_rec.marital_status)));
v_nationality := upper(rtrim(ltrim(i_rec.national_identifier)));
v_father_name := NVL(LTRIM(RTRIM(INITCAP(i_rec.father_name))),NULL);
dbms_output.put_line(‘Employee:’||v_first_name||’-‘||v_employee_number);
–1) getting Person_type_id Begin
BEGIN
Select Person_Type_Id
Into v_Person_Type_Id
FROM per_person_types
Where upper(user_person_type) = ltrim(rtrim(upper(i_rec.persontype)))
And business_group_id =81;– fnd_profile.value(‘PER_BUSINESS_GROUP_ID’);
Err_Flag := ‘Y’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Err_Flag := ‘N’;
Err_Mess := ‘Person Type’||I_REC.PersonType||’ does not exist in PER_PERSON_TYPES.’;
END;
IF I_REC.MARITAL_STATUS IS NOT NULL THEN
BEGIN
SELECT LOOKUP_CODE
INTO v_marital_status
FROM APPS.FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE=’MARITAL_STATUS’
AND UPPER(MEANING)=upper(rtrim(ltrim(i_rec.marital_status)))
AND ROWNUM=1;
EXCEPTION WHEN OTHERS THEN
ERR_FLAG := ‘N’;
ERR_Mess := ERR_Mess || ‘INVALID MARITAL STATUS ‘ || i_rec.marital_status ;
END;
END IF;
–1) getting Person_type_id End;
IF Instr(Err_Flag,’N’) > 0 THEN — This is begin if for employee creation 1
— Updating the EGO_PER_PEOPLE_F_INT table with status_flag = N and with proper error message.
UPDATE XX_PER_ALL_PEOPLE_F_INT
SET Status_flag = ‘N’ ,
Interface_date = Sysdate ,
Message = Err_Mess||’ THIS RECORD IS NOT INSERTED TO PER_ALL_PEOPLE_F’
WHERE ROWID = I_REC.ROWID;
Count1 := Count1 + 1;
— reinitializing for checking next record.
Err_Flag := null;
Err_Mess := null;
Else
DBMS_OUTPUT.PUT_LINE(‘GENDER :’||UPPER(i_rec.gender));
— Block Name (Person Type Id Block) Over
/********************************************Employee Creation Begin *******************************/
HR_EMPLOYEE_API.CREATE_EMPLOYEE(
p_validate => v_validate,
p_hire_date => v_hire_date,
p_business_group_id => v_business_group_id,
p_last_name => v_last_name,
p_sex => upper(i_rec.gender),
p_person_type_id => v_person_type_id,
p_date_of_birth => v_birth_date,
p_email_address => v_email_address,
p_employee_number => v_employee_number,
p_first_name => v_first_name,
p_marital_status => v_marital_status,
p_middle_names => v_mi_name,
p_title => v_titledesc,
p_nationality => v_national_identifier,
— p_national_identifier => v_national_identifier,
p_person_id => v_person_id, — out parameter
p_assignment_id => v_assignment_id, — out parameter
p_per_object_version_number => v_per_object_version_number, — out parameter
p_asg_object_version_number => v_asg_object_version_number, — out parameter
p_per_effective_start_date => v_per_effective_start_date, — out parameter
p_per_effective_end_date => v_per_effective_end_date, — out parameter
p_full_name => v_full_name, — out parameter
p_per_comment_id => v_per_comment_id, — out parameter
p_assignment_sequence => v_assignment_sequence, — out parameter
p_assignment_number => v_assignment_number, — out parameter
p_name_combination_warning => v_name_combination_warning, — out parameter
p_assign_payroll_warning => v_assign_payroll_warning, — out parameter
p_orig_hire_warning => v_orig_hire_warning — out parameter
);
DBMS_OUTPUT.PUT_LINE(‘OLD EMP NO:’||I_REC.employee_no);
DBMS_OUTPUT.PUT_LINE(‘NEW EMP NO:’||v_employee_number);
— commit; — for employee creation.
— Variable Declaration for Emp Addr creation begin
BEGIN
v_effective_date := NULL;
v_date_from := NULL;
v_city := NULL;
v_state := NULL;
v_county := NULL;
v_home_telno := NULL;
v_address_id := NULL;
v_object_version_number := NULL;
— 2) getting Person_id Begin
Begin
Select person_id,
business_group_id
Into v_person_id,
v_business_group_id
From per_all_people_f
Where Employee_Number = v_employee_number;
Err_Flag := ‘Y’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Err_Flag := ‘N’;
Err_Mess := ‘person id, business group id’||’ does not exist in PER_ALL_PEOPLE_F.’;
END;
— getting person id is over.
— 3) Getting Effective start date begin
BEGIN
SELECT a.effective_start_date
INTO v_effective_date
FROM per_all_people_f a
WHERE a.person_id = v_person_id
AND object_version_number = (SELECT max(b.object_version_number)
FROM per_all_people_f b
WHERE b.person_id = v_person_id)
AND effective_start_date = (SELECT max(c.effective_start_date)
FROM per_all_people_f c
WHERE c.person_id = v_person_id);
Err_Flag := ‘Y’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Err_Flag := ‘N’;
Err_Mess := ‘Effective Start Date ‘||’,’||’ does not exist in PER_ALL_PEOPLE_F.’;
END;
— 3) getting effective start date is over.
–4) getting address id, ovn begin
— this block is not necessary
–4) Getting address id,object version number is over.
IF Instr(Err_Flag,’N’) > 0 THEN — This is begin if for address creation 2 if
— Updating the EGO_PER_PEOPLE_F_INT table with status_flag = N and with proper error message.
UPDATE XX_PER_ALL_PEOPLE_F_INT
SET Status_flag = ‘N’ ,
Interface_date = Sysdate ,
Message = Err_Mess||’ THIS RECORD IS NOT INSERTED TO PER_ALL_PEOPLE_F’
WHERE ROWID = I_REC.ROWID;
Count1 := Count1 + 1;
Delete from PER_ALL_PEOPLE_F
WHERE PERSON_ID = v_person_id;
Commit;
— reinitializing for checking next record.
Err_Flag := null;
Err_Mess := null;
Else
BEGIN
hr_person_address_api.cre_or_upd_person_address(
p_validate => v_validate,
p_effective_date => v_effective_date,
p_person_id => v_person_id,
p_business_group_id => v_business_group_id,
p_style => ‘GENERIC’,
p_primary_flag => v_primary_flag,
p_pradd_ovlapval_override => TRUE,
p_date_from => v_effective_date,
p_address_line1 => INITCAP(i_rec.address_line1),
p_address_line2 => INITCAP(i_rec.address_line2),
p_town_or_city => INITCAP(i_rec.town_or_city),
p_region_2 => INITCAP(i_rec.StateName),
p_postal_code => i_rec.postal_code,
p_country => i_rec.countryName,
p_telephone_number_1 => i_rec.HOMEPHONENUMBER,
p_address_id => v_address_id,
p_object_version_number => v_object_version_number
);
END; — for creation of address
COMMIT;
END IF;–END OF ADDRESS CREATION
END;–END OF ADDRESS AND ASSIGNMENT
END IF;–end of employee if creation
Count2 := Count2 + 1;
— Updating the XX_PER_ALL_PEOPLE_F table status_flag =’Y’ to indicate the
— row successfully inserted in PER_.. TABLES.
UPDATE XX_PER_ALL_PEOPLE_F_INT
SET Status_Flag = ‘Y’ ,
Interface_date = Sysdate ,
Message = Null
WHERE ROWID = I_REC.ROWID;
— reinitializing for checking next record.
Err_Flag := null;
Err_Mess :=null;
END LOOP;
/* fnd_file.put_line(fnd_file.log,’ “VALIDATION REPORT” ‘);
fnd_file.put_line(fnd_file.log,’Number of records inserted into PER_ALL_PEOPLE_F TABLE are ‘||Count2);
fnd_file.put_line(fnd_file.log,’Number of records NOT inserted into PER_ALL_PEOPLE_F TABLE due to errors are ‘||Count1);
fnd_file.put_line(fnd_file.log,’_________________________________________________________________________________’);
fnd_file.put_line(fnd_file.log,’ERRORS : ‘);*/
dbms_output.put_line(‘ “VALIDATION REPORT” ‘);
dbms_output.put_line(‘Number of records inserted into PER_ALL_PEOPLE_F TABLE are ‘||Count2);
dbms_output.put_line(‘Number of records NOT inserted into PER_ALL_PEOPLE_F TABLE due to errors are ‘||Count1);
dbms_output.put_line(‘_________________________________________________________________________________’);
dbms_output.put_line(‘ERRORS : ‘);
–This code is written to print the error message
BEGIN
FOR II IN CUR0
LOOP
NUM :=NUM+1;
LEN_MESSAGE := LENGTH(II.MESSAGE);
SELECT CEIL(LEN_MESSAGE/253)
INTO NUMBER_LOG
FROM XX_PER_ALL_PEOPLE_F_INT
WHERE ROWID = II.ROWID ;
IF LENGTH(II.MESSAGE)<=253 THEN
fnd_file.put_line(fnd_file.log,NUM||’.’||II.MESSAGE);
ELSE
FOR X IN 1..(NUMBER_LOG+1)
LOOP
B:=B+A;
IF X = 1 then
B:=1;
A := Instr(SUBSTR(II.MESSAGE,B,253),’ ‘,-1);
–fnd_file.put_line(fnd_file.log,NUM||’.’||SUBSTR(II.MESSAGE,B,A));
dbms_output.put_line(SUBSTR(II.MESSAGE,B,A));
ELSIF
X=(NUMBER_LOG+1) THEN
–fnd_file.put_line(fnd_file.log,SUBSTR(II.MESSAGE,B));
dbms_output.put_line(SUBSTR(II.MESSAGE,B));
ELSE
A := Instr(SUBSTR(II.MESSAGE,B,253),’ ‘,-1);
–fnd_file.put_line(fnd_file.log,SUBSTR(II.MESSAGE,B,A));
dbms_output.put_line(SUBSTR(II.MESSAGE,B));
END IF;
END LOOP;
END IF;
END LOOP; –END OF CURSOR CX
END;
COMMIT;
–EXCEPTION WHEN OTHERS THEN
— RAISE;
END XX_create_employee;
procedure XX_create_emp_assign is
CURSOR cx IS
Select
STATUS_FLAG,
MESSAGE,
INTERFACE_DATE,
EMPLOYEE_No,
A.ROWID ,
SUPERVISOR_NO
FROM XX_PER_ALL_PEOPLE_F_INT A,per_all_people_f B
WHERE A.EMPLOYEE_NO=B.EMPLOYEE_NUMBER AND STATUS_FLAG =’Y’;
v_person_id NUMBER;
v_business_group_id NUMBER;
err_flag VARCHAR2(1);
err_mess VARCHAR2(4000);
v_object_version_number NUMBER;
v_assignment_id NUMBER;
v_effective_date DATE;
v_org_id NUMBER;
v_set_of_books_id NUMBER;
v_supervisor_id NUMBER;
V_cagr_grade_def_id per_cagr_grades_def.cagr_grade_def_id%TYPE;
V_cagr_concatenated_segments VARCHAR2(2000);
v_comment_id NUMBER;
v_concatenated_segments VARCHAR2(2000);
v_no_managers_warning BOOLEAN;
v_effective_start_date DATE;
v_effective_end_date DATE;
v_soft_coding_keyflex_id NUMBER;
v_assignment_number NUMBER;
v_datetrack_update_mode Varchar2(200):= ‘CORRECTION’;
v_assignment_status_type_id NUMBER;
COUNT1 NUMBER;
v_validate BOOLEAN := FALSE;
V_other_manager_warning BOOLEAN;
BEGIN
FOR i_rec IN cx
LOOP
dbms_output.put_line(‘here’||i_rec.employee_no);
Begin
Select person_id,
business_group_id
Into v_person_id,
v_business_group_id
From per_all_people_f
Where Employee_Number = i_rec.employee_no;
Err_Flag := ‘Y’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Err_Flag := ‘N’;
Err_Mess := ‘person id, business group id’||’ does not exist in PER_ALL_PEOPLE_F.’;
END;
/*CREATION OF ASSIGNEMENT*/
dbms_output.put_line(‘PERSON ID ‘||v_person_id);
dbms_output.put_line(‘supervisor no ‘||i_rec.supervisor_no);
IF i_rec.supervisor_no IS NOT NULL AND v_person_id IS NOT NULL THEN
dbms_output.put_line(‘PERSON ID 1 ‘||v_person_id);
dbms_output.put_line(‘supervisor no 2 ‘||i_rec.supervisor_no);
— GETTING OBJECT VERSION NUMBER, ASSIGNMENT ID AND EFFECTIVE DATE BEGIN
— Object version no block begin
— get
Begin
SELECT a.object_version_number, a.assignment_id, a.effective_start_date
INTO v_object_version_number, v_assignment_id, v_effective_date
FROM per_all_assignments_f a
WHERE a.person_id = v_person_id
AND object_version_number = (SELECT max(b.object_version_number)
FROM per_all_assignments_f b
WHERE b.person_id = a.person_id)
AND effective_start_date = (SELECT max(c.effective_start_date)
FROM per_all_assignments_f c
WHERE c.person_id = a.person_id);
Err_Flag := ‘Y’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Err_Flag := ‘N’;
Err_Mess := ‘object version number’ ||’ does not exist in PER_ALL_ASSIGNMENTS_F’;
END;
Begin
Select organization_id,set_of_books_id
Into v_org_id,
v_set_of_books_id
From hr_operating_units
Where business_group_id = v_business_group_id
AND ORGANIZATION_ID=85;
— AND ROWNUM=1;
Err_Flag := ‘Y’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Err_Flag := ‘N’;
Err_Mess := ‘ORG_ID,SOB ERROR’;
END;
V_ASSIGNMENT_STATUS_TYPE_ID :=1;
–GETTING SUPERVISOR DETAILS
DBMS_OUTPUT.PUT_LINE(‘supervisor_no:’||i_rec.supervisor_no);
Begin
Select person_id/*,
business_group_id*/
Into v_supervisor_id /*,
v_business_group_id*/
From per_all_people_f
Where Employee_Number = to_char(i_rec.supervisor_no);
Err_Flag := ‘Y’;
EXCEPTION
WHEN NO_DATA_FOUND THEN
Err_Flag := ‘N’;
Err_Mess := ‘Supervisor does not exist in PER_ALL_PEOPLE_F.’;
END;
IF Instr(Err_Flag,’N’) > 0 THEN — This is begin if for emp asg creation 3
— Updating the EGO_PER_PEOPLE_F_INT table with status_flag = N and with proper error message.
UPDATE XX_PER_ALL_PEOPLE_F_INT
SET Status_flag = ‘N’ ,
Interface_date = Sysdate ,
Message = Err_Mess||’ THIS RECORD IS NOT INSERTED TO PER_ALL_ASSIGNMENTS_F’
WHERE ROWID = I_REC.ROWID;
— delete the incomplete employee,address creation records for emp id.
Delete from PER_ALL_PEOPLE_F
WHERE PERSON_ID = v_person_id;
Delete from per_addresses
WHERE PERSON_ID = v_person_id;
COMMIT;
Count1 := Count1 + 1;
— reinitializing for checking next record.
Err_Flag := null;
Err_Mess := null;
Else
dbms_output.put_line(‘in else’);
BEGIN
hr_assignment_api.update_emp_asg
(p_validate => v_validate
,p_effective_date => v_effective_date
,p_datetrack_update_mode => v_datetrack_update_mode
,p_assignment_id => v_assignment_id
,p_object_version_number => v_object_version_number
,p_supervisor_id => v_supervisor_id
,p_assignment_number => v_assignment_number
,p_change_reason => null
,p_comments => null
,p_date_probation_end => null
,p_default_code_comb_id => null
,p_frequency => null
,p_internal_address_line => null
,p_manager_flag => null
,p_normal_hours => null
,p_perf_review_period => null
,p_perf_review_period_frequency => null
,p_probation_period => null
,p_probation_unit => null
,p_sal_review_period => null
,p_sal_review_period_frequency => null
,p_set_of_books_id => v_set_of_books_id
,p_source_type => null
,p_time_normal_finish => null
,p_time_normal_start => null
,p_bargaining_unit_code => null
,p_labour_union_member_flag => null
,p_hourly_salaried_code => null
,p_cagr_grade_def_id => V_cagr_grade_def_id
,p_cagr_concatenated_segments => V_cagr_concatenated_segments
,p_soft_coding_keyflex_id => V_soft_coding_keyflex_id
,p_comment_id => V_comment_id
,p_effective_start_date => v_effective_start_date
,p_effective_end_date => v_effective_end_date
,p_concatenated_segments => V_concatenated_segments
,p_no_managers_warning => V_no_managers_warning
,p_other_manager_warning => V_other_manager_warning
);
END;
END IF;–END OF ASSIGNEMENT
END IF;–END OF SUPERVISOR
END LOOP;
END;
END;
Recent Posts
