Help other and Put API required for HRMS

[alruwaished]

This is the first API to upload your data to HRMS files
Basic employee date API
Create table Employee_temp
(
hire_date date
, first_name varchar2(30)
, father_name varchar2(30)
, Grandfather_name varchar2(30)
, family_name varchar2(30)
, date_of_birth date
, town_of_birth varchar2(50)
, employee_number varchar2(15)
, nationality varchar2(10) --nationality
, national_identifier varchar2(30)
, religion varchar2(50) --Employee religion
, attribute1 varchar2(100) --Arabic name
, attribute2 varchar2(15) --Date of Issue ID NO
, attribute3 varchar2(50) --place of Issue ID NO , attribute4 varchar2(50) --Place of Living
, attribute5 varchar2(5) --NO. of Children
, attribute6 varchar2(15) --Applicant GOSI NO
, attribute7 varchar2(15) --Medical Insurance Number
, attribute8 varchar2(30) --Medical insurance Class
, attribute9 varchar2(30) --No. Travel Ticket
, attribute10 varchar2(100) --Raute of Travel
, additional_first_name varchar2(30)
, additional_father_name varchar2(30)
, additional_Grandfather_name varchar2(30)
, additional_family_name varchar2(30)
)




----------------------------------------------------------------------------------------------------------
declare
-- Boolean parameters are translated from/to integers:
-- 0/1/null <--> false/true/null
-- p_validate boolean := sys.diutil.int_to_bool(:p_validate);
p_name_combination_warning boolean;
p_assign_payroll_warning boolean;
p_orig_hire_warning boolean;
p_person_id number;
p_assignment_id varchar2(100);
p_per_object_version_number number;
p_asg_object_version_number number;
p_per_effective_start_date date;
p_per_effective_end_date date;
p_full_name varchar2(100);
p_per_comment_id number;
p_assignment_sequence number;
p_assignment_number number;


cursor emp is select t.*
,Decode (t.nationality,'PQH_5',substr(t.national_identifier,1,1)||'-'||substr(t.national_identifier,2,4)||'-'||
substr(t.national_identifier,6,4)||'-'||substr(t.national_identifier,10,1),null) ID
from Employee_temp t
where t.upload is null
and t.employee_number not in (select distinct employee_number from per_all_people_f )

order by employee_number;
begin
-- Call the procedure
Insert into fnd_sessions values(userenv('sessionid'),sysdate);
for sal in Emp loop

hr_sa_employee_api.create_sa_employee(
-- p_validate => False ,
p_hire_date => Sal.hire_date,
p_business_group_id => 81,
p_family_name => sal.family_name,
p_sex => 'M',
p_person_type_id => 1120,
p_date_of_birth => Sal.date_of_birth,
p_employee_number => sal.employee_number,
p_first_name => sal.first_name,
p_nationality => sal.nationality,
p_national_identifier => sal.id,
p_title => 'MR.',
p_attribute1 => Sal.attribute1,
p_attribute2 => Sal.attribute2,
p_attribute3 => Sal.attribute3,
p_attribute4 => Sal.attribute4,
p_attribute5 => Sal.attribute5,
p_attribute6 => Sal.attribute6,
p_attribute7 => Sal.attribute7,
p_attribute8 => Sal.attribute8,
p_attribute9 => Sal.attribute9,
p_attribute10 =>ltrim(rtrim(Sal.attribute10)),
p_hijrah_birth_date =>hr_sa_hijrah_functions.gregorian_to_hijrah(Sal.date_of_birth),
p_hijrah_hire_date =>hr_sa_hijrah_functions.gregorian_to_hijrah(Sal.hire_date),
p_father_name =>Sal.father_name,
p_grandfather_name => Sal.grandfather_name,
p_alt_first_name => Sal.additional_first_name,
p_alt_father_name => Sal.additional_father_name,
p_alt_grandfather_name => Sal.additional_grandfather_name,
p_alt_family_name => Sal.additional_family_name,
p_religion => Sal.religion,
p_town_of_birth => Sal.town_of_birth,

------ out parameter------
p_person_id => p_person_id,
p_assignment_id => p_assignment_id,
p_per_object_version_number => p_per_object_version_number,
p_asg_object_version_number => p_asg_object_version_number,
p_per_effective_start_date => p_per_effective_start_date,
p_per_effective_end_date => p_per_effective_end_date,
p_full_name => p_full_name,
p_per_comment_id => p_per_comment_id,
p_assignment_sequence => p_assignment_sequence,
p_assignment_number => p_assignment_number,
p_name_combination_warning => p_name_combination_warning,
p_assign_payroll_warning => p_assign_payroll_warning,
p_orig_hire_warning => p_orig_hire_warning);

commit ;
end loop;

-- Convert false/true/null to 0/1/null
/*:p_name_combination_warning := sys.diutil.bool_to_int(p_name_combination_warning);
:p_assign_payroll_warning := sys.diutil.bool_to_int(p_assign_payroll_warning);
:p_orig_hire_warning := sys.diutil.bool_to_int(p_orig_hire_warning);*/
end;





















====================================================================================================================


procedure create_sa_employee
(p_validate in boolean default false
,p_hire_date in date
,p_business_group_id in number
,p_family_name in varchar2
,p_sex in varchar2
,p_person_type_id in number default null
,p_comments in varchar2 default null
,p_date_employee_data_verified in date default null
,p_date_of_birth in date default null
,p_email_address in varchar2 default null
,p_employee_number in out nocopy varchar2
,p_expense_check_send_to_addres in varchar2 default null
,p_first_name in varchar2
,p_known_as in varchar2 default null
,p_marital_status in varchar2 default null
,p_nationality in varchar2
,p_national_identifier in varchar2 default null
,p_previous_last_name in varchar2 default null
,p_registered_disabled_flag in varchar2 default null
,p_title in varchar2
,p_vendor_id in number default null
,p_work_telephone in varchar2 default null
,p_attribute_category in varchar2 default null
,p_attribute1 in varchar2 default null
,p_attribute2 in varchar2 default null
,p_attribute3 in varchar2 default null
,p_attribute4 in varchar2 default null
,p_attribute5 in varchar2 default null
,p_attribute6 in varchar2 default null
,p_attribute7 in varchar2 default null
,p_attribute8 in varchar2 default null
,p_attribute9 in varchar2 default null
,p_attribute10 in varchar2 default null
,p_attribute11 in varchar2 default null
,p_attribute12 in varchar2 default null
,p_attribute13 in varchar2 default null
,p_attribute14 in varchar2 default null
,p_attribute15 in varchar2 default null
,p_attribute16 in varchar2 default null
,p_attribute17 in varchar2 default null
,p_attribute18 in varchar2 default null
,p_attribute19 in varchar2 default null
,p_attribute20 in varchar2 default null
,p_attribute21 in varchar2 default null
,p_attribute22 in varchar2 default null
,p_attribute23 in varchar2 default null
,p_attribute24 in varchar2 default null
,p_attribute25 in varchar2 default null
,p_attribute26 in varchar2 default null
,p_attribute27 in varchar2 default null
,p_attribute28 in varchar2 default null
,p_attribute29 in varchar2 default null
,p_attribute30 in varchar2 default null
,p_father_name in varchar2 default null
,p_grandfather_name in varchar2 default null
,p_alt_first_name in varchar2
,p_alt_father_name in varchar2 default null
,p_alt_grandfather_name in varchar2 default null
,p_alt_family_name in varchar2
,p_religion in varchar2
,p_hijrah_birth_date in varchar2 default null
,p_hijrah_hire_date in varchar2 default null
,p_date_of_death in date default null
,p_background_check_status in varchar2 default null
,p_background_date_check in date default null
,p_blood_type in varchar2 default null
,p_correspondence_language in varchar2 default null
,p_fast_path_employee in varchar2 default null
,p_fte_capacity in number default null
,p_honors in varchar2 default null
,p_internal_location in varchar2 default null
,p_last_medical_test_by in varchar2 default null
,p_last_medical_test_date in date default null
,p_mailstop in varchar2 default null
,p_office_number in varchar2 default null
,p_on_military_service in varchar2 default null
,p_projected_start_date in date default null
,p_resume_exists in varchar2 default null
,p_resume_last_updated in date default null
,p_second_passport_exists in varchar2 default null
,p_student_status in varchar2 default null
,p_work_schedule in varchar2 default null
,p_benefit_group_id in number default null
,p_receipt_of_death_cert_date in date default null
,p_coord_ben_med_pln_no in varchar2 default null
,p_coord_ben_no_cvg_flag in varchar2 default 'N'
,p_coord_ben_med_ext_er in varchar2 default null
,p_coord_ben_med_pl_name in varchar2 default null
,p_coord_ben_med_insr_crr_name in varchar2 default null
,p_coord_ben_med_insr_crr_ident in varchar2 default null
,p_coord_ben_med_cvg_strt_dt in date default null
,p_coord_ben_med_cvg_end_dt in date default null
,p_uses_tobacco_flag in varchar2 default null
,p_dpdnt_adoption_date in date default null
,p_dpdnt_vlntry_svce_flag in varchar2 default 'N'
,p_original_date_of_hire in date default null
,p_adjusted_svc_date in date default null
,p_town_of_birth in varchar2 default null
,p_region_of_birth in varchar2 default null
,p_country_of_birth in varchar2 default null
,p_global_person_id in varchar2 default null
,p_person_id out nocopy number
,p_assignment_id out nocopy number
,p_per_object_version_number out nocopy number
,p_asg_object_version_number out nocopy number
,p_per_effective_start_date out nocopy date
,p_per_effective_end_date out nocopy date
,p_full_name out nocopy varchar2
,p_per_comment_id out nocopy number
,p_assignment_sequence out nocopy number
,p_assignment_number out nocopy varchar2
,p_name_combination_warning out nocopy boolean
,p_assign_payroll_warning out nocopy boolean
,p_orig_hire_warning out nocopy boolean)


declare
-- Boolean parameters are translated from/to integers:
-- 0/1/null <--> false/true/null
-- p_validate boolean := sys.diutil.int_to_bool(:p_validate);
p_name_combination_warning boolean;
p_assign_payroll_warning boolean;
p_orig_hire_warning boolean;
p_person_id number;
p_assignment_id varchar2(100);
p_per_object_version_number number;
p_asg_object_version_number number;
p_per_effective_start_date date;
p_per_effective_end_date date;
p_full_name varchar2(100);
p_per_comment_id number;
p_assignment_sequence number;
p_assignment_number number;


cursor emp is select t.* ,Decode (t.nationality,'PQH_5',substr(t.national_identifier,1,1)||'-'||substr(t.national_identifier,2,4)||'-'||
substr(t.national_identifier,6,4)||'-'||substr(t.national_identifier,10,1),null) ID from Employee_temp t
where t.employee_number not in (select employee_number from per_all_people_f where employee_number is not null ) ;


begin
-- Call the procedure
--Insert into fnd_sessions values(userenv('sessionid'),sysdate);

for sal in Emp

loop

hr_sa_employee_api.create_sa_employee(
p_validate => True,
p_hire_date => Sal.hire_date,
p_business_group_id => 81,
p_family_name => sal.family_name,
p_sex => 'M',
p_person_type_id => 1120,
p_date_of_birth => Sal.date_of_birth,
p_employee_number => sal.employee_number,
p_first_name => sal.first_name,
p_nationality => sal.nationality,
p_national_identifier => sal.id,
p_title => 'MR.',
p_attribute1 => Sal.attribute1,
p_attribute2 => Sal.attribute2,
p_attribute3 => Sal.attribute3,
-- p_attribute4 => Sal.attribute4,
p_attribute5 => Sal.attribute5,
p_attribute6 => Sal.attribute6,
p_attribute7 => Sal.attribute7,
p_attribute8 => Sal.attribute8,
p_attribute9 => Sal.attribute9,
p_attribute10 =>ltrim(rtrim(Sal.attribute10)),
p_hijrah_birth_date =>hr_sa_hijrah_functions.gregorian_to_hijrah(Sal.date_of_birth),
p_hijrah_hire_date =>hr_sa_hijrah_functions.gregorian_to_hijrah(Sal.hire_date),
p_father_name =>Sal.father_name,
p_grandfather_name => Sal.grandfather_name,
p_alt_first_name => Sal.additional_first_name,
p_alt_father_name => Sal.additional_father_name,
p_alt_grandfather_name => Sal.additional_grandfather_name,
p_alt_family_name => Sal.additional_family_name,
p_religion => 'M',
p_town_of_birth => Sal.town_of_birth,

------ out parameter------
p_person_id => p_person_id,
p_assignment_id => p_assignment_id,
p_per_object_version_number => p_per_object_version_number,
p_asg_object_version_number => p_asg_object_version_number,
p_per_effective_start_date => p_per_effective_start_date,
p_per_effective_end_date => p_per_effective_end_date,
p_full_name => p_full_name,
p_per_comment_id => p_per_comment_id,
p_assignment_sequence => p_assignment_sequence,
p_assignment_number => p_assignment_number,
p_name_combination_warning => p_name_combination_warning,
p_assign_payroll_warning => p_assign_payroll_warning,
p_orig_hire_warning => p_orig_hire_warning);

commit ;
end loop;

-- Convert false/true/null to 0/1/null
/*:p_name_combination_warning := sys.diutil.bool_to_int(p_name_combination_warning);
:p_assign_payroll_warning := sys.diutil.bool_to_int(p_assign_payroll_warning);
:p_orig_hire_warning := sys.diutil.bool_to_int(p_orig_hire_warning);*/
end;