PROCEDURE xx_element_creation_proc (
p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY NUMBER,
p_effective_start_date IN VARCHAR2,
p_effective_end_date IN VARCHAR2
)
AS
CURSOR cur_asg_emp (
p_effective_start_date IN DATE,
p_effective_end_date IN DATE
)
IS
SELECT DISTINCT emp_no
FROM xx_time_sheet
WHERE work_date BETWEEN TO_DATE (p_effective_start_date)
AND TO_DATE (p_effective_end_date)
AND last_run_date IS NULL;
--Declare Variables
lv_proceed VARCHAR2 (1) := 'Y';
ln_ot NUMBER := 0;
ln_sot NUMBER := 0;
ln_tardi NUMBER := 0;
ln_flexi NUMBER := 0;
lv_error_message VARCHAR2 (300) := NULL;
ln_group_code NUMBER;
ln_assignment_id NUMBER;
ln_tardi_time NUMBER;
ln_tardi_hrs NUMBER;
ln_ot_hrs NUMBER;
ln_sot_hrs NUMBER;
ln_input_value_ot NUMBER;
ln_input_value_sot NUMBER;
ln_input_value_tar NUMBER;
ln_ot_link_id NUMBER;
ld_effective_start_date DATE;
ld_effective_end_date DATE;
ln_element_entry_id NUMBER;
ln_object_version_number NUMBER;
lb_create_warning BOOLEAN;
ln_sot_link_id NUMBER;
ln_tardi_link_id NUMBER;
ln_bg_id NUMBER
:= fnd_profile.VALUE ('Business_Group_ID');
ld_effective_date DATE;
lv_ot_eligibility VARCHAR2 (4);
ld_doj DATE;
BEGIN
SELECT TRUNC (TO_DATE (p_effective_end_date), 'Month')
INTO ld_effective_date
FROM DUAL;
FOR cur_rec IN cur_asg_emp (p_effective_start_date, p_effective_end_date)
LOOP
--Reset Variables
lv_proceed := 'Y';
ln_ot := 0;
ln_sot := 0;
ln_tardi := 0;
ln_flexi := 0;
lv_error_message := NULL;
ln_group_code := NULL;
ln_assignment_id := NULL;
ln_tardi_time := 0;
ln_tardi_hrs := 0;
ln_ot_hrs := 0;
ln_sot_hrs := 0;
ln_input_value_ot := NULL;
ln_input_value_sot := NULL;
ln_input_value_tar := NULL;
ln_ot_link_id := NULL;
ld_effective_start_date := NULL;
ld_effective_end_date := NULL;
ln_element_entry_id := NULL;
ln_object_version_number := NULL;
lb_create_warning := NULL;
ln_sot_link_id := NULL;
ln_tardi_link_id := NULL;
lv_ot_eligibility := NULL;
ld_doj := NULL;
-----Calculate Timesheet Data
BEGIN
SELECT ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.over_time)
)
),
2
) over_time,
ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.special_over_time)
)
),
2
) special_over_time,
ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.tardiness_time)
)
),
2
) tardiness_time,
ROUND
(xx_seconds_to_hrs
(SUM (xx_hours_in_seconds (xxm.flex_hours)
)
),
2
) flexi_hours
INTO ln_ot,
ln_sot,
ln_tardi,
ln_flexi
FROM xx_time_sheet xxm
WHERE emp_no = cur_rec.emp_no
AND work_date BETWEEN TO_DATE (p_effective_start_date)
AND TO_DATE (p_effective_end_date)
GROUP BY emp_no;
EXCEPTION
WHEN OTHERS
THEN
lv_error_message := 'Timesheet Data Issue. Check the data';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
END;
--Decode for group id
BEGIN
SELECT NVL (DECODE (paaf.people_group_id,
61, 3,
62, 2,
63, 1,
paaf.people_group_id
),
0
) "group_code",
paaf.assignment_id, paaf.ass_attribute1 ot_eligibility,
papf.original_date_of_hire
INTO ln_group_code,
ln_assignment_id, lv_ot_eligibility,
ld_doj
FROM per_all_people_f papf, per_all_assignments_f paaf
WHERE paaf.person_id = papf.person_id
AND p_effective_end_date BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND p_effective_end_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND papf.employee_number = cur_rec.emp_no;
IF ln_group_code = 0
THEN
lv_error_message := 'Please enter people group for the employee';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
END IF;
IF ld_doj > ld_effective_date
THEN
ld_effective_date := ld_doj;
END IF;
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Error in getting people group. Check the data';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
END;
IF ln_group_code = 2 AND NVL (lv_ot_eligibility, 'N') = 'N'
THEN
ln_tardi_time := (ln_tardi + ln_flexi) - (ln_ot + ln_sot);
lv_proceed := 'N';
IF ln_tardi_time > 0
THEN
lv_proceed := 'Y';
ln_tardi_hrs := ln_tardi_time;
END IF;
ELSIF ln_group_code = 2 AND NVL (lv_ot_eligibility, 'N') = 'Y'
THEN
IF ln_ot >= ln_flexi
THEN
ln_ot_hrs := ln_ot - ln_flexi;
ln_sot_hrs := ln_sot;
ln_tardi_hrs := ln_tardi;
ELSIF ln_ot < ln_flexi
THEN
IF (ln_ot + ln_sot) >= ln_flexi
THEN
ln_ot_hrs := 0;
ln_sot_hrs := (ln_ot + ln_sot) - ln_flexi;
ln_tardi_hrs := ln_tardi;
ELSE
ln_ot_hrs := 0;
ln_sot_hrs := 0;
ln_tardi_hrs := (ln_tardi + ln_flexi) - (ln_ot + ln_sot);
END IF;
END IF;
ELSIF ln_group_code = 1
THEN
lv_error_message := 'No Element Creation for Managers';
lv_proceed := 'N';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
ELSE
ln_ot_hrs := ln_ot;
ln_sot_hrs := ln_sot;
ln_tardi_hrs := ln_tardi;
END IF;
BEGIN
SELECT input_value_id
INTO ln_input_value_ot
FROM pay_input_values_f pi, pay_element_types_f pe
WHERE pi.NAME = 'Total Hours'
AND pi.element_type_id = pe.element_type_id
AND pe.element_name = 'Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message := 'More than one Hours input is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
WHEN OTHERS
THEN
lv_error_message := 'Error in getting Hours Input ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
END;
BEGIN
SELECT input_value_id
INTO ln_input_value_sot
FROM pay_input_values_f pi, pay_element_types_f pe
WHERE pi.NAME = 'Total Hours'
AND pi.element_type_id = pe.element_type_id
AND pe.element_name = 'Special Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message := 'More than one Hours input is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
WHEN OTHERS
THEN
lv_error_message := 'Error in getting Hours Input ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
END;
BEGIN
SELECT input_value_id
INTO ln_input_value_tar
FROM pay_input_values_f pi, pay_element_types_f pe
WHERE pi.NAME = 'Hours'
AND pi.element_type_id = pe.element_type_id
AND pe.element_name = 'Tardiness';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message := 'More than one Hours input is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
WHEN OTHERS
THEN
lv_error_message := 'Error in getting Hours Input ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
lv_proceed := 'N';
END;
IF ln_ot_hrs > 0 AND lv_proceed = 'Y'
THEN --Create Element entry for Over Time.
BEGIN
SELECT pelf.element_link_id
INTO ln_ot_link_id
FROM pay_element_types_f petf, pay_element_links_f pelf
WHERE petf.element_type_id = pelf.element_type_id
AND petf.element_name = 'Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message :=
'More than one Overtime element is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
--lv_proceed = 'N' ;
NULL;
WHEN OTHERS
THEN
lv_error_message := 'Error in getting OT link ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
-- lv_proceed = 'N' ;
NULL;
END;
--Call element creation API
BEGIN
pay_element_entry_api.create_element_entry
(p_effective_date => ld_effective_date,
p_business_group_id => ln_bg_id,
p_assignment_id => ln_assignment_id
--p_assignment_id
,
p_element_link_id => ln_ot_link_id,
p_entry_type => 'E' --For Entry
,
p_input_value_id1 => ln_input_value_ot
--Input Value ID for Hours
,
p_entry_value1 => ln_ot_hrs,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
UPDATE xx_time_sheet
SET last_run_date = SYSDATE
WHERE emp_no = cur_rec.emp_no;
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| 'Overtime Element Created ~ '
|| ln_ot_hrs
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message := 'Error in OT element creation - API Error';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| lv_error_message
|| ' ~ '
|| SUBSTR (SQLERRM, 1, 90)
);
END;
END IF;
IF ln_sot_hrs > 0 AND lv_proceed = 'Y'
THEN --Create element entry for Special Over Time.
BEGIN
SELECT pelf.element_link_id
INTO ln_sot_link_id
FROM pay_element_types_f petf, pay_element_links_f pelf
WHERE petf.element_type_id = pelf.element_type_id
AND petf.element_name = 'Special Overtime';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message :=
'More than one Special Overtime element is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
--lv_proceed = 'N' ;
NULL;
WHEN OTHERS
THEN
lv_error_message := 'Error in getting SOT link ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
-- lv_proceed = 'N' ;
NULL;
END;
BEGIN
pay_element_entry_api.create_element_entry
(p_effective_date => ld_effective_date,
p_business_group_id => ln_bg_id,
p_assignment_id => ln_assignment_id
--p_assignment_id
,
p_element_link_id => ln_sot_link_id,
p_entry_type => 'E' --For entry
,
p_input_value_id1 => ln_input_value_sot
--Input Value ID for Hours
,
p_entry_value1 => ln_sot_hrs,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
UPDATE xx_time_sheet
SET last_run_date = SYSDATE
WHERE emp_no = cur_rec.emp_no;
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| 'Special Overtime Element Created ~ '
|| ln_sot_hrs
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Error in SOT element creation - API Error';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| lv_error_message
|| ' ~ '
|| SUBSTR (SQLERRM, 1, 90)
);
END;
END IF;
IF ln_tardi_hrs > 0 AND lv_proceed = 'Y'
THEN --Create element entry for Special Over Time.
BEGIN
SELECT pelf.element_link_id
INTO ln_tardi_link_id
FROM pay_element_types_f petf, pay_element_links_f pelf
WHERE petf.element_type_id = pelf.element_type_id
AND petf.element_name = 'Tardiness';
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
lv_error_message :=
'More than one Tardiness element is defined';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
--lv_proceed = 'N' ;
NULL;
WHEN OTHERS
THEN
lv_error_message := 'Error in getting OT link ID';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no || ' ~ ' || lv_error_message
);
-- lv_proceed = 'N' ;
NULL;
END;
BEGIN
pay_element_entry_api.create_element_entry
(p_effective_date => ld_effective_date,
p_business_group_id => ln_bg_id,
p_assignment_id => ln_assignment_id
--p_assignment_id
,
p_element_link_id => ln_tardi_link_id,
p_entry_type => 'E' --For Entry
,
p_input_value_id1 => ln_input_value_tar
--Input Value ID for Hours
,
p_entry_value1 => ln_tardi_hrs,
p_effective_start_date => ld_effective_start_date,
p_effective_end_date => ld_effective_end_date,
p_element_entry_id => ln_element_entry_id,
p_object_version_number => ln_object_version_number,
p_create_warning => lb_create_warning
);
UPDATE xx_time_sheet
SET last_run_date = SYSDATE
WHERE emp_no = cur_rec.emp_no;
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| 'Tardiness Element Created ~ '
|| ln_tardi_hrs
);
EXCEPTION
WHEN OTHERS
THEN
lv_error_message :=
'Error in Tardi element creation - API Error';
fnd_file.put_line (fnd_file.LOG,
cur_rec.emp_no
|| ' ~ '
|| lv_error_message
|| ' ~ '
|| SUBSTR (SQLERRM, 1, 90)
);
END;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Fatal Error' || ' ~ ' || SUBSTR (SQLERRM, 1, 60)
);
p_retcode := 3;
END xx_element_creation_proc;
-----------------------------------------------------------------------------------------------------------------------------------
--Element Creation - End
Showing posts with label Pay_Element_Entry_Api.create_element_entry Oracle HRMS Element Entry information. Show all posts
Showing posts with label Pay_Element_Entry_Api.create_element_entry Oracle HRMS Element Entry information. Show all posts
Tuesday, December 7, 2010
Subscribe to:
Posts (Atom)