HRMS API create_element_entry

CREATE OR REPLACE PROCEDURE APPS.XX_TIME_ATTE_UPLOAD( errbuf VARCHAR2,
retcode NUMBER)

IS
l_effective_start_date date:=NULL;
l_effective_end_date date:=NULL;
l_element_entry_id number:=NULL;
l_object_version_number number:=NULL ;
l_create_warning boolean:=NULL;
element_link_id number;
element_pay_value_id number;
element_assignment_id number;
l_business_group_id NUMBER;

error_msg varchar2(1000);
l_total_records number := 0;
l_success_records number := 0;
l_failure_records number := 0;

--Declare Cursor
cursor c is
SELECT TO_CHAR (TO_DATE (pay_period, 'YYYYMM'), 'DD-MON-YYYY') effective_date,
location_batch_number, employee_number,
record_number_for_same_employe,time_attendance_code,
CASE
WHEN time_attendance_code = 'TDN'
THEN 'Lateness'
WHEN time_attendance_code = 'ABU'
THEN 'Unauthorized leave'
WHEN time_attendance_code = 'SLV'
THEN 'Sick Leave'
WHEN time_attendance_code = 'DEA'
THEN 'Death Leave'
WHEN time_attendance_code = 'EAA'
THEN 'Eid AL Adha Holiday'
WHEN time_attendance_code = 'EAF'
THEN 'EID ul Fitr Holiday'
WHEN time_attendance_code = 'IL'
THEN 'Industrial Injury'
WHEN time_attendance_code = 'MAR'
THEN 'Marriage Leave'
WHEN time_attendance_code = 'NAT'
THEN 'National day '
WHEN time_attendance_code = 'NEW'
THEN 'New born leave'
WHEN time_attendance_code = 'OPH'
THEN 'Optional holiday'
WHEN time_attendance_code = 'PIL'
THEN 'Hajj Leave'
WHEN time_attendance_code = 'SPV'
THEN 'Sports Leave'
WHEN time_attendance_code = 'STU'
THEN 'Study leave'
WHEN time_attendance_code = 'ULN'
THEN 'Leave without pay'
WHEN time_attendance_code = 'OTH'
THEN 'Overtime - Holiday'
WHEN time_attendance_code = 'OTL'
THEN 'Overtime - Leave'
WHEN time_attendance_code = 'OTR'
THEN 'Overtime - Regular'
WHEN time_attendance_code = 'OTT'
THEN 'Overtime - Ramadan'
WHEN time_attendance_code = 'OTV'
THEN 'Overtime - Previous Month'
WHEN time_attendance_code = 'OTW'
THEN 'Overtime - Weekend'
ELSE time_attendance_code
END AS element_name,
number_of_days, hours_minutes, upload_date, upload_time, updated_by
FROM xxscc_hr_ta_summary_data
where --status<>'PROCESSED'
employee_number=101958 ;

p_data c%rowtype;
l_data_value number := null;

BEGIN

FND_FILE.PUT_LINE(FND_FILE.log,'#############################################################');
FND_FILE.PUT_LINE(FND_FILE.log,'Leave Details Of The Employees :');
FND_FILE.PUT_LINE(FND_FILE.log,'#############################################################');
FND_FILE.PUT_LINE(FND_FILE.log,'Start Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));

SELECT business_group_id
INTO l_business_group_id
FROM per_business_groups
WHERE name = 'Saudi Cement Company';
OPEN c;
LOOP
element_link_id := null;
element_pay_value_id := null;
element_assignment_id := null;
error_msg := null;

FETCH c INTO p_data;
EXIT WHEN c%NOTFOUND;

l_total_records := l_total_records + 1;

-- Fetch Element Link Id for Basic Pay
BEGIN
select element_link_id
into element_link_id
from pay_element_types_f pt,
pay_element_links_f pl
where pt.element_type_id = pl.element_type_id
and pt.business_group_id =l_business_group_id
and upper(pt.element_name) = upper(nvl(p_data.element_name,pt.element_name))
and pl.effective_end_date = '31-DEC-4712';

-- Fetch Input Value Id for Basic Pay
select input_value_id
into element_pay_value_id
from pay_element_types_f pet,
pay_input_values_f piv
where pet.element_type_id= piv.element_type_id
and pet.business_group_id =l_business_group_id
and upper(piv.name)like
(select upper(piv.name) from pay_input_values_f piv,pay_element_types_f pet
where pet.element_type_id= piv.element_type_id
and pet.business_group_id =l_business_group_id
AND piv.DISPLAY_SEQUENCE=1
and upper(pet.element_name) = upper(nvl(p_data.element_name,pet.element_name))
and piv.effective_end_date = '31-DEC-4712')
and upper(pet.element_name) = upper(nvl(p_data.element_name,pet.element_name))
and piv.effective_end_date = '31-DEC-4712';

--Fetch Assignment ID for Basic Pay
select paaf.assignment_id
into element_assignment_id
from
per_all_people_f papf,
per_all_assignments_f paaf
where
papf.person_id = paaf.person_id
AND sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE
AND sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE
AND papf.EMPLOYEE_NUMBER=nvl(p_data.employee_number,papf.EMPLOYEE_NUMBER);
EXCEPTION
WHEN OTHERS THEN
l_failure_records := l_failure_records+1;
FND_FILE.PUT_LINE(FND_FILE.log, 'Exception raised during opration' );
FND_FILE.PUT_LINE(FND_FILE.log, SUBSTR(SQLERRM,1,200));
error_msg := SUBSTR(SQLERRM,1,200);
UPDATE XXSCC_HR_TA_SUMMARY_DATA SET ERROR_MESSAGE=error_msg ,status = 'ERROR'
WHERE employee_number = p_data.employee_number
AND time_attendance_code=p_data.time_attendance_code;
commit;
END;

BEGIN

if p_data.time_attendance_code='TDN'
then l_data_value := p_data.hours_minutes;
elsif p_data.time_attendance_code='ABU'
then l_data_value := p_data.number_of_days;
elsif p_data.time_attendance_code='SLV'
then l_data_value := p_data.number_of_days;
else
l_data_value := p_data.number_of_days;
end if;


pay_element_entry_api.create_element_entry
(p_validate =>false
,p_effective_date =>p_data.effective_date
,p_business_group_id =>l_business_group_id
,p_assignment_id =>element_assignment_id
,p_element_link_id =>element_link_id --in number
,p_entry_type =>'E' --in Entry_Type in HR_lookups Table
,p_input_value_id1 =>element_pay_value_id -- this has to be fetched from element link table
,p_entry_value1 =>l_data_value
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_object_version_number
,p_create_warning => l_create_warning --out nocopy boolean
);
FND_FILE.PUT_LINE(FND_FILE.log,'Employee Number'||p_data.employee_number);

if l_element_entry_id is not null then
UPDATE XXSCC_HR_TA_SUMMARY_DATA SET status = 'PROCESSED',ERROR_MESSAGE=NULL
WHERE employee_number = p_data.employee_number
AND time_attendance_code=p_data.time_attendance_code;
commit;
l_success_records := l_success_records + 1;
end if;

EXCEPTION
WHEN OTHERS THEN
l_failure_records := l_failure_records+1;
FND_FILE.PUT_LINE(FND_FILE.log, 'Exception raised during opration' );
FND_FILE.PUT_LINE(FND_FILE.log, SUBSTR(SQLERRM,1,200));
error_msg := SUBSTR(SQLERRM,1,200);
UPDATE XXSCC_HR_TA_SUMMARY_DATA SET ERROR_MESSAGE=error_msg ,status = 'ERROR'
WHERE employee_number = p_data.employee_number
AND time_attendance_code=p_data.time_attendance_code;
commit;
END;

DBMS_OUTPUT.PUT_LINE( 'ELEMNENT ENTRY ID IS '|| l_element_entry_id );
end loop;
FND_FILE.PUT_LINE(FND_FILE.log,'End Time : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS'));
FND_FILE.PUT_LINE(FND_FILE.log,'#############################################################');
FND_FILE.PUT_LINE(FND_FILE.log,'Total Records To Be Loaded : ' || l_total_records);
FND_FILE.PUT_LINE(FND_FILE.log,'Total Success Records : ' || l_success_records);
FND_FILE.PUT_LINE(FND_FILE.log,'Total Failure Records : ' || l_failure_records);
FND_FILE.PUT_LINE(FND_FILE.log,'#############################################################');
close c;
end;
/