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
Hi! I just planned to discover you ever get complications with hackers?
ReplyDeleteThis previous blog (wordpress blogs) had been broken in to
i appeared losing months of diligence due to simply no back.
Do you possess virtually any methods to end cyberpunks?
Here is my webpage ... Online tarot readings
*Timesheet plays a significant role in this as it processes the amount of time worked by the employee and based on this their wages are calculated. In the case of web based timesheet the data should be well encrypted before sending from the client system to the central server hence the software should use comparability secure "https" protocol for authentication and other data manipulation purpose
ReplyDeleteMonthly Timesheet Template When the payroll and/or billing cycle is monthly, you need a monthly timesheet to record such data. During that period of time, you can record detailed data about tasks completed in a project. Information which is important when it comes to billing your clients, paying employee salaries, managing costs, tracking projects and estimating completion times, and other tasks integral to business operations. Azor offers professionally designed monthly timesheet templates that may not only solve your time management problems. But with a little adaptation of such a template to suit the way work is done at your workplace, you will be able to bring in more productivity to the workplace.
ReplyDeleteThank you for posting this blog I, in fact, cherished it and located up some precise blogs about oracle........go to our net internet page related to oracle fusion procurement all modules education
ReplyDeleteThanks for sharing this form of useful information
oracle fusion procurement online training
oracle fusion procurement training
Simply wish to say your article is as astonishing. The clarity in your post is simply great, and I could assume you are an expert on this subject. Same as your blog i found another one Oracle Fusion Procurement .Actually I was looking for the same information on internet for Oracle Fusion Procurement and came across your blog. I am impressed by the information that you have on this blog. Thanks a million and please keep up the gratifying work.
ReplyDelete