CREATE OR REPLACE procedure APPS.ifl_emp_absenc_monthly ( errbuff VARCHAR2,
retcode VARCHAR2,
p_tkp NUMBER,
p_date_from VARCHAR2,
p_date_to VARCHAR2,
p_user_id NUMBER ) as
begin
ifl_emp_absence_upload_monthly.emp_sandwitch_absence_process( p_tkp ,
p_date_from ,
p_date_to ,
p_user_id );
/*ifl_emp_absence_upload_monthly.process_sshr_absence ( p_tkp ,
p_date_from ,
p_date_to ,
p_user_id );*/
DBMS_OUTPUT.put_line ('<<<<<<<<<< Absence Process Staus >>>>>>>>>');
DBMS_OUTPUT.put_line ('Successfully Processd Absence');
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '/********************* Sandwitch Absence Process Staus Start *************************/');
fnd_file.put_line (fnd_file.LOG, ' Successfully Processd Absence ' );
fnd_file.put_line (fnd_file.LOG, '/********************* Sandwitch Absence Process Staus End *************************/');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('<<<<<<<<<< Exception>>>>>>>>>');
DBMS_OUTPUT.put_line ('Erros Message :'|| SUBSTR (SQLERRM, 1, 99));
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< Exception >>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,
('Erros Message :'|| SUBSTR (SQLERRM, 1, 99))
);
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< End >>>>>>>>>');
end;
/
retcode VARCHAR2,
p_tkp NUMBER,
p_date_from VARCHAR2,
p_date_to VARCHAR2,
p_user_id NUMBER ) as
begin
ifl_emp_absence_upload_monthly.emp_sandwitch_absence_process( p_tkp ,
p_date_from ,
p_date_to ,
p_user_id );
/*ifl_emp_absence_upload_monthly.process_sshr_absence ( p_tkp ,
p_date_from ,
p_date_to ,
p_user_id );*/
DBMS_OUTPUT.put_line ('<<<<<<<<<< Absence Process Staus >>>>>>>>>');
DBMS_OUTPUT.put_line ('Successfully Processd Absence');
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '/********************* Sandwitch Absence Process Staus Start *************************/');
fnd_file.put_line (fnd_file.LOG, ' Successfully Processd Absence ' );
fnd_file.put_line (fnd_file.LOG, '/********************* Sandwitch Absence Process Staus End *************************/');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('<<<<<<<<<< Exception>>>>>>>>>');
DBMS_OUTPUT.put_line ('Erros Message :'|| SUBSTR (SQLERRM, 1, 99));
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< Exception >>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,
('Erros Message :'|| SUBSTR (SQLERRM, 1, 99))
);
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< End >>>>>>>>>');
end;
/
CREATE OR REPLACE PACKAGE APPS.ifl_emp_absence_upload_monthly
IS
FUNCTION get_off_shift (lv_work_start_date VARCHAR2, p_person_id NUMBER)
RETURN CHAR;
FUNCTION per_attendnce_typ (p_person_id NUMBER, p_date DATE)
RETURN VARCHAR2;
FUNCTION emp_gazet_day_yn (p_ear_polc_id NUMBER, p_date DATE)
RETURN CHAR;
FUNCTION get_last_present (p_person_id NUMBER, p_date DATE)
RETURN DATE;
FUNCTION get_gazete_days (
p_person_id NUMBER,
p_date DATE,
p_tk_group_id NUMBER
)
RETURN DATE;
FUNCTION get_rest_days (p_person_id NUMBER, p_date DATE, p_group_id NUMBER)
RETURN DATE;
FUNCTION get_absence_type (
p_person_id NUMBER,
p_date DATE,
p_group_id NUMBER
)
RETURN NUMBER;
PROCEDURE emp_process_absence ( --errbuff VARCHAR2,
p_tkp NUMBER, -- retcode VARCHAR2,
p_date_from VARCHAR2,
p_date_to VARCHAR2,
p_user_id NUMBER
);
PROCEDURE emp_sandwitch_absence_process ( --errbuff VARCHAR2,
p_tkp NUMBER, -- retcode VARCHAR2,
p_date_from VARCHAR2,
p_date_to VARCHAR2,
p_user_id NUMBER
);
procedure process_sshr_absence
( p_tkp NUMBER,
p_date_from VARCHAR2,
p_date_to VARCHAR2,
p_user_id NUMBER
);
END;
/
CREATE OR REPLACE PACKAGE BODY APPS.ifl_emp_absence_upload_monthly
IS
FUNCTION get_off_shift (lv_work_start_date VARCHAR2, p_person_id NUMBER)
RETURN CHAR
IS
v_shift VARCHAR2 (20);
BEGIN
SELECT DISTINCT --hs.ID ,
hs.NAME
INTO v_shift
-- hrp.NAME rotation_plan,
-- hws.WEEK_DAY,
-- hs.STANDARD_START,
-- hs.STANDARD_STOP,hrp.NAME,to_Date(hrs.START_DATE)+(hws.SEQ_NO-1) work_date
FROM hxt_add_assign_info_f hasif,
per_all_assignments_f paaf,
per_all_people_f papf,
pay_people_groups ppg,
hxt_rotation_plans hrp, --- rotation plan,
hxt_rotation_schedules hrs, -- rotation plan detail,
hxt_weekly_work_schedules hwws, --- work plan,
hxt_work_shifts hws, -- work plan detail,
hxt_shifts hs, -- shifts
hxt_earning_policies hep
WHERE 1 = 1
AND papf.person_id = paaf.person_id
AND hasif.assignment_id = paaf.assignment_id
AND ppg.people_group_id = paaf.people_group_id
AND hep.ID = hasif.earning_policy
AND hrp.ID = hasif.rotation_plan
AND hrp.ID = hrs.rtp_id
AND hrs.tws_id = hwws.ID
AND hwws.ID = hws.tws_id
AND hws.sht_id = hs.ID
AND SYSDATE BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND paaf.primary_flag = 'Y'
AND SYSDATE BETWEEN hep.effective_start_date
AND hep.effective_end_date
/* AND hrs.start_date =
(SELECT MAX (hrs1.start_date)
FROM hxt_rotation_schedules hrs1
WHERE hrs1.rtp_id = hrs.rtp_id
AND hrs1.start_date <= lv_work_start_date) */ -- change tanveer 06 sep2016
-- :lv_work_start_date
AND TO_DATE (hrs.start_date) + (hws.seq_no - 1) =lv_work_start_date
AND SYSDATE BETWEEN hasif.effective_start_date
AND hasif.effective_end_date
AND papf.person_id = p_person_id;
--AND HS.STANDARD_START =0
-- and upper(hs.NAME) in ('OFF SHIFT') ;
RETURN v_shift;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
FUNCTION per_attendnce_typ (p_person_id NUMBER, p_date DATE)
RETURN VARCHAR2
IS
v_leave VARCHAR2 (50);
BEGIN
SELECT DECODE (aat.NAME,
'Annual Leave', 'AL',
'Sick Leave', 'SL',
'Casual Leaves', 'CL',
'ABSENCE', 'A',
aat.NAME
)
INTO v_leave
FROM hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc,
per_absence_attendance_types aat,
per_absence_attendances paa
WHERE paa.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND paa.absence_attendance_type_id = aat.absence_attendance_type_id
AND paa.person_id = p_person_id
AND p_date BETWEEN paa.date_start AND paa.date_end
AND paa.absence_days <> 0;
RETURN NVL (v_leave, 'A');
EXCEPTION
WHEN OTHERS
THEN
RETURN 'A';
END;
FUNCTION emp_gazet_day_yn (p_ear_polc_id NUMBER, p_date DATE)
RETURN CHAR
IS
v_gh CHAR (3);
BEGIN
SELECT DISTINCT CASE
WHEN hc.holiday_date = p_date
THEN 'Yes'
ELSE 'No'
END
INTO v_gh
FROM hxt_holiday_days hc, hxt_earning_policies ep
WHERE ep.hcl_id = hc.hcl_id
AND hc.holiday_date = p_date
AND ep.ID = p_ear_polc_id;
RETURN NVL (v_gh, 'No');
EXCEPTION
WHEN OTHERS
THEN
RETURN 'No';
END;
FUNCTION get_last_present (p_person_id NUMBER, p_date DATE)
RETURN DATE
IS
time_date DATE;
leave_date DATE;
BEGIN
SELECT MAX (TRUNC (tbb.start_time))
INTO time_date -- time keeper find last date
FROM hxc_time_building_blocks tbb
WHERE tbb.resource_id = p_person_id
AND tbb.SCOPE = 'DETAIL'
AND TRUNC (tbb.start_time) between trunc(p_date,'MON')-5 and p_date
--and tbb.UNIT_OF_MEASURE = 'HOURS'
---AND TBB.PARENT_BUILDING_BLOCK_ID =1
AND TBB.OBJECT_VERSION_NUMBER = 1
AND approval_status = 'WORKING';
SELECT MAX (paa.date_end)
INTO leave_date
FROM hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc,
per_absence_attendance_types aat,
per_absence_attendances paa
WHERE paa.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND paa.absence_attendance_type_id = aat.absence_attendance_type_id
AND paa.date_end between trunc(p_date,'MON')-5 and p_date
--AND htg.tk_group_id = :P_GROUP_ID
AND paa.person_id = p_person_id
AND paa.absence_days <> 0;
IF time_date > leave_date and leave_date is not null then
RETURN time_date;
elsif leave_date is null
THEN
RETURN time_date;
ELSE
RETURN leave_date;
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
FUNCTION get_gazete_days (
p_person_id NUMBER,
p_date DATE,
p_tk_group_id NUMBER
)
RETURN DATE
IS
v_gazet_day DATE;
v_present_day date;
BEGIN
v_present_day := get_last_present (p_person_id ,p_date);
SELECT MIN (hc.holiday_date)
INTO v_gazet_day
FROM hxt_holiday_days hc,
hxt_earning_policies ep,
hxt_add_assign_info_f aai,
per_assignments_x pa,
hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc
WHERE 1 = 1
AND ep.hcl_id = hc.hcl_id
AND aai.earning_policy = ep.ID
AND aai.assignment_id = pa.assignment_id
AND pa.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND pa.person_id = p_person_id
AND hc.holiday_date BETWEEN v_present_day
AND TO_DATE (p_date) - 1
AND htg.tk_group_id = p_tk_group_id
GROUP BY pa.person_id, pa.business_group_id;
RETURN v_gazet_day;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
FUNCTION get_rest_days (p_person_id NUMBER, p_date DATE, p_group_id NUMBER)
RETURN DATE
IS
v_rest_day DATE;
v_present_day date;
BEGIN
v_present_day := get_last_present (p_person_id, p_date) ;
SELECT --- REST DAY EMPLOYEE KIP ABSENCE
MAX ((rs.start_date) + (ws.seq_no - 1))
INTO v_rest_day
FROM hxt_rotation_plans rp,
hxt_add_assign_info_f aai,
per_assignments_x pa,
hxt_rotation_schedules rs,
hxt_work_shifts ws,
hxt_shifts hs,
hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc
WHERE 1 = 1
AND aai.rotation_plan = rp.ID
AND rs.rtp_id = rp.ID
AND ws.tws_id = rs.tws_id
AND rp.date_to IS NULL
AND ws.sht_id = hs.ID
AND aai.assignment_id = pa.assignment_id
AND (rs.start_date) + (ws.seq_no - 1)
BETWEEN v_present_day
AND TO_DATE (p_date) - 1
AND upper(hs.NAME) like 'OFF SHIFT%'
AND pa.person_id = p_person_id
AND pa.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND htg.tk_group_id = p_group_id;
RETURN v_rest_day;
EXCEPTION
WHEN OTHERS
THEN
RETURN NULL;
END;
FUNCTION get_absence_type (
p_person_id NUMBER,
p_date DATE,
p_group_id NUMBER
)
RETURN NUMBER
IS
v_leave_type NUMBER;
v_gazet_day DATE;
v_rest_day DATE;
BEGIN
v_gazet_day := get_gazete_days (p_person_id, p_date, p_group_id);
v_rest_day := get_rest_days (p_person_id, p_date, p_group_id);
IF v_gazet_day >= v_rest_day
THEN
/* SELECT aat.input_value_id
INTO v_leave_type
FROM hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc,
per_absence_attendance_types aat,
per_absence_attendances paa
WHERE paa.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND paa.absence_attendance_type_id =
aat.absence_attendance_type_id
AND paa.date_end = TO_DATE (v_rest_day) - 1
AND htg.tk_group_id = p_group_id
AND paa.person_id = p_person_id
AND paa.absence_days <> 0;*/
select la.ABSENC_ID
INTO v_leave_type
from ifl_leave_absence la
where la.PERSON_ID = p_person_id
and la.ABSENT_DATE = TO_DATE (v_rest_day) - 1;
RETURN NVL (v_leave_type, 0);
ELSIF v_gazet_day IS NULL AND v_rest_day IS NOT NULL
THEN
/* SELECT aat.input_value_id
INTO v_leave_type
FROM hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc,
per_absence_attendance_types aat,
per_absence_attendances paa
WHERE paa.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND paa.absence_attendance_type_id =
aat.absence_attendance_type_id
AND paa.date_end = TO_DATE (v_rest_day) - 1
AND htg.tk_group_id = p_group_id
AND paa.person_id = p_person_id
AND paa.absence_days <> 0;*/
select la.ABSENC_ID
INTO v_leave_type
from ifl_leave_absence la
where la.PERSON_ID = p_person_id
and la.ABSENT_DATE = TO_DATE (v_rest_day) - 1;
RETURN NVL (v_leave_type, 0);
ELSIF v_gazet_day IS NOT NULL AND v_rest_day IS NULL
THEN
/* SELECT aat.input_value_id
INTO v_leave_type
FROM hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc,
per_absence_attendance_types aat,
per_absence_attendances paa
WHERE paa.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND paa.absence_attendance_type_id =
aat.absence_attendance_type_id
AND paa.date_end = TO_DATE (v_gazet_day) - 1
AND htg.tk_group_id = p_group_id
AND paa.person_id = p_person_id
AND paa.absence_days <> 0;*/
select la.ABSENC_ID
INTO v_leave_type
from ifl_leave_absence la
where la.PERSON_ID = p_person_id
and la.ABSENT_DATE = TO_DATE (v_gazet_day) - 1;
RETURN NVL (v_leave_type, 0);
ELSIF v_rest_day >= v_gazet_day
THEN
/* SELECT aat.input_value_id
INTO v_leave_type
FROM hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc,
per_absence_attendance_types aat,
per_absence_attendances paa
WHERE paa.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND paa.absence_attendance_type_id =
aat.absence_attendance_type_id
AND paa.date_end = TO_DATE (v_gazet_day) - 1
AND htg.tk_group_id = p_group_id
AND paa.person_id = p_person_id
AND paa.absence_days <> 0;*/
select la.ABSENC_ID
INTO v_leave_type
from ifl_leave_absence la
where la.PERSON_ID = p_person_id
and la.ABSENT_DATE = TO_DATE (v_gazet_day) - 1;
RETURN NVL (v_leave_type, 0);
ELSE
/* SELECT aat.input_value_id
INTO v_leave_type
FROM hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc,
per_absence_attendance_types aat,
per_absence_attendances paa
WHERE paa.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND paa.absence_attendance_type_id =
aat.absence_attendance_type_id
AND paa.date_end = TO_DATE (p_date) - 1
AND htg.tk_group_id = p_group_id
AND paa.person_id = p_person_id
AND paa.absence_days <> 0;*/
select la.ABSENC_ID
INTO v_leave_type
from ifl_leave_absence la
where la.PERSON_ID = p_person_id
and la.ABSENT_DATE = TO_DATE (p_date) - 1;
RETURN NVL (v_leave_type, 0);
END IF;
EXCEPTION
WHEN OTHERS
THEN
RETURN 0;
END;
PROCEDURE emp_process_absence ( --errbuff VARCHAR2,
p_tkp NUMBER, -- retcode VARCHAR2,
p_date_from VARCHAR2,
p_date_to VARCHAR2,
p_user_id NUMBER
)
AS
absance_id NUMBER;
v_gazet_days DATE;
v_rest_days DATE;
v_type VARCHAR2 (30);
last_present DATE;
v_absence_type VARCHAR2 (50);
v_net_value NUMBER (15, 2);
p_payroll NUMBER;
p_assignment_id NUMBER;
v_leave_type NUMBER;
v_absenc_id NUMBER;
error_msg VARCHAR2 (500);
v_start_date DATE;
v_end_date DATE;
v_days NUMBER;
v_date_from DATE
:= fnd_conc_date.string_to_date (p_date_from);
v_date_to DATE
:= fnd_conc_date.string_to_date (p_date_to);
--TO_CHAR(fnd_conc_date.string_to_date (p_work_start_date),'DD-MON-YYYY');
p_group NUMBER := p_tkp;
CURSOR c1
IS
select * from (
select to_number(TR.EMPLOYEEID) person_id, 0 business_group_id, -- IF WORKING HOURS LESS THAN 4
tr.attendancedate absence_start_date,
tr.attendancedate absence_end_date
from tad_register tr ,
hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc
where tr.ATTENDANCETYPE = 'P'
AND to_number(TR.EMPLOYEEID) = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND htg.tk_group_id = p_tkp
AND tr.attendancedate BETWEEN v_date_from AND v_date_to
AND ROUND(ABS((to_number(to_char(tr.CLOCKIN,'HH24'))+(to_number(to_char(tr.CLOCKIN,'MI'))/60)) -
(to_number(to_char(tr.CLOCKOUT,'HH24'))+(to_number(to_char(tr.CLOCKOUT,'MI'))/60))),2)<4
UNION ALL
SELECT ppf.person_id, paf.business_group_id,
tr.attendancedate absence_start_date,
tr.attendancedate absence_end_date
FROM per_people_f ppf,
per_assignments_f paf,
per_positions pp,
per_position_definitions ppd,
tad_register tr,
hxt_add_assign_info_f aai,
hxt_shifts hs,
hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc
WHERE 1 = 1
AND paf.position_id = pp.position_id
AND pp.position_definition_id = ppd.position_definition_id
AND paf.person_id = tr.employeeid
AND TO_CHAR (hs.ID) = TO_CHAR (aai.attribute1)
AND paf.assignment_id = aai.assignment_id
--and paf.ASSIGNMENT_STATUS_TYPE_ID =1 -- assignment type
AND tr.attendancedate BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND tr.attendancedate BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND tr.attendancedate BETWEEN v_date_from AND v_date_to
AND ppf.person_id = paf.person_id
AND paf.primary_flag = 'Y'
AND paf.person_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND htg.tk_group_id = p_tkp
AND tr.ATTENDANCETYPE = 'A' -- MISSING BOTH TIME
AND per_attendnce_typ (paf.person_id, tr.attendancedate) =
'A'
-- ABSENCE
AND get_off_shift (tr.attendancedate, paf.person_id) NOT LIKE
('Off Shift%') -- EXCLUDE REST DAY
AND emp_gazet_day_yn (aai.earning_policy, tr.attendancedate) =
'No'
-- EXCLUDE GAZET DAY
AND (paf.person_id, tr.attendancedate) NOT IN (
SELECT distinct htbb.resource_id p_id, TRUNC (htbb.start_time) dat
FROM hxc_time_building_blocks htbb,
hxc_tk_groups htg,
hxc_tk_group_queries htgq,
hxc_tk_group_query_criteria htgqc
WHERE htbb.resource_id = paf.person_id
AND htbb.SCOPE = 'DETAIL'
AND htbb.approval_status = 'WORKING'
--- AND HTBB.PARENT_BUILDING_BLOCK_ID =1
AND HTBB.OBJECT_VERSION_NUMBER = 1
AND htbb.resource_id = htgqc.criteria_id
AND htgq.tk_group_query_id = htgqc.tk_group_query_id
AND htg.tk_group_id = htgq.tk_group_id
AND htg.tk_group_id = p_tkp
AND TRUNC (htbb.start_time) BETWEEN v_date_from
AND v_date_to
union all
select la.PERSON_ID p_id,la.ABSENT_DATE dat
from ifl_leave_absence la
where la.ABSENT_DATE between v_date_from and v_date_to
))
-- auto gen employee exclude
ORDER BY absence_start_date ASC, person_id;
BEGIN
-------------------------------------------------
fnd_global.apps_initialize (p_user_id,23128,809);
FOR i IN c1
LOOP
BEGIN
v_days := (TO_DATE (i.absence_end_date) - TO_DATE (i.absence_start_date)) + 1;
v_absenc_id := 12066 ;
v_absence_type := 'ABSENCE';
INSERT INTO ifl_leave_absence
(person_id, last_present, TYPE,
gazet_days, rest_days, absent_date,
leave_type, absence_type, absenc_id,
start_date, end_date, days, last_update_date,
last_updated_by, created_by, creation_date
)
VALUES (i.person_id, last_present, 'Absence Leave Type',
v_gazet_days, v_rest_days, i.absence_start_date,
v_leave_type, v_absence_type, v_absenc_id,
i.absence_start_date,i.absence_end_date, v_days, SYSDATE,
p_user_id, p_user_id, SYSDATE
);
/* DBMS_OUTPUT.put_line ('<<<<<<<< Absence Process employee <<>>>>>>>>>');
DBMS_OUTPUT.put_line ('person_id & Absence Date :' || i.person_id ||' Start Date '||i.absence_start_date||'End Date'||i.absence_end_date);
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
--fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< Absence Process employee >>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,'person_id & Absence Date :' || i.person_id ||' Start Date '||i.absence_start_date||' End Date'||i.absence_end_date);
--fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< end >>>>>>>>>'); */
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
error_msg := SUBSTR (SQLERRM, 1, 99);
DBMS_OUTPUT.put_line ('<<<<<<<<<< Exception>>>>>>>>>');
DBMS_OUTPUT.put_line ('person_id & Absence Date :' || i.person_id ||' Start Date '||i.absence_start_date||'End Date'||i.absence_end_date);
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< Exception >>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,
error_msg||' '||'person_id & Absence Date :' || i.person_id ||' Start Date '||i.absence_start_date||'End Date'||i.absence_end_date);
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< End >>>>>>>>>');
END;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('<<<<<<<<<<absence_upload>>>>>>>>>');
DBMS_OUTPUT.put_line ('<<<<<<<<<<''absence' || SQLERRM || '>>>>>>>>>'
);
DBMS_OUTPUT.put_line ('<<<<<<<<<<absence_upload>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<<absence_upload>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,
'<<<<<<<<<<' || SQLERRM || '>>>>>>>>>'
);
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<<absence_upload>>>>>>>>>');
END;
procedure emp_sandwitch_absence_process
( p_tkp NUMBER,
p_date_from VARCHAR2,
p_date_to VARCHAR2,
p_user_id NUMBER
) as
absance_id NUMBER;
v_gazet_days DATE;
v_rest_days DATE;
v_type VARCHAR2 (30);
last_present DATE;
v_absence_type VARCHAR2 (50);
v_net_value NUMBER (15, 2);
p_payroll NUMBER;
p_assignment_id NUMBER;
v_leave_type NUMBER;
v_absenc_id NUMBER;
error_msg VARCHAR2 (500);
v_start_date DATE;
v_end_date DATE;
v_days NUMBER;
v_date_from DATE
:= fnd_conc_date.string_to_date (p_date_from);
v_date_to DATE
:= fnd_conc_date.string_to_date (p_date_to);
--TO_CHAR(fnd_conc_date.string_to_date (p_work_start_date),'DD-MON-YYYY');
p_group NUMBER := p_tkp;
cursor c_sr
is
select la.PERSON_ID ,
la.START_DATE absence_start_date,
la.END_DATE absence_end_date
from ifl_leave_absence la
where la.STATUS_FLAG is null
and la.ABSENT_DATE between v_date_from and v_date_to
order by la.ABSENT_DATE asc;
c_emp c_sr%ROWTYPE ;
begin
/************ process only absence ***********************/
emp_process_absence (p_tkp,p_date_from,p_date_to,p_user_id) ;
commit;
open c_sr;
loop
fetch c_sr into c_emp;
exit when c_sr%notfound;
begin
last_present :=get_last_present (c_emp.person_id, c_emp.absence_start_date);
v_gazet_days :=get_gazete_days (c_emp.person_id, c_emp.absence_start_date, p_tkp);
v_rest_days :=get_rest_days (c_emp.person_id,c_emp.absence_start_date, p_tkp);
v_leave_type :=get_absence_type (c_emp.person_id,c_emp.absence_start_date, p_tkp);
v_absence_type := 'ABSENCE';
---IFL_MARK_ABSENCE_TYPE(i.p_person_id,v_date_worked,p_tkp) ; to be changed unpaid leave mark
v_absenc_id := 12066;
--IFL_GET_ABSENCE_TYPE_ID('ABSENCE'); ---V_ABSENCE_TYPE);
IF v_leave_type = 0 AND c_emp.absence_start_date <> last_present
THEN
v_start_date := c_emp.absence_start_date;
v_end_date := c_emp.absence_end_date;
ELSIF (v_leave_type > 0 AND v_rest_days >= v_gazet_days ) --v_gazet_days >= v_rest_days 07 sep 2016
OR ( v_leave_type > 0
AND v_gazet_days IS NULL
AND v_rest_days IS NOT NULL
)
AND c_emp.absence_start_date <> last_present
AND last_present <> v_rest_days
THEN
v_start_date := v_rest_days;
v_end_date := c_emp.absence_end_date;
ELSIF v_leave_type > 0 AND v_gazet_days >=v_rest_days ---v_rest_days >= v_gazet_days 07 sep 2016
OR ( v_leave_type > 0
AND v_rest_days IS NULL
AND v_gazet_days IS NOT NULL
)
AND c_emp.absence_start_date <> last_present
AND last_present <> v_gazet_days
THEN
v_start_date := v_gazet_days;
v_end_date := c_emp.absence_end_date;
ELSIF c_emp.absence_start_date = last_present
THEN
v_start_date := NULL;
v_end_date := NULL;
ELSE
v_start_date := c_emp.absence_start_date;
v_end_date := c_emp.absence_end_date;
END IF;
v_days := (TO_DATE (v_end_date) - TO_DATE (v_start_date)) + 1;
INSERT INTO IFL_ABSENCE_sw_rule
(person_id, last_present, TYPE,
gazet_days, rest_days, absent_date,
leave_type, absence_type, absenc_id,
start_date, end_date, days, last_update_date,
last_updated_by, created_by, creation_date
)
VALUES (c_emp.person_id, last_present, 'Sandwitch Leave Type',
v_gazet_days, v_rest_days, c_emp.absence_start_date,
v_leave_type, v_absence_type, v_absenc_id,
v_start_date,v_end_date, v_days, SYSDATE,
p_user_id, p_user_id, SYSDATE
);
DBMS_OUTPUT.put_line ('<<<<<<<< sanwitch Absence Process employee <<>>>>>>>>>');
DBMS_OUTPUT.put_line ('person_id & Absence Date :' || c_emp.person_id ||' Start Date '||c_emp.absence_start_date||'End Date'||c_emp.absence_end_date);
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< sanwitch Absence Process employee >>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,'person_id & Absence Date :' || c_emp.person_id ||' Start Date '||c_emp.absence_start_date||' End Date'||c_emp.absence_end_date);
--fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< end >>>>>>>>>');
COMMIT;
if v_days is not null then
update ifl_leave_absence la
set la.STATUS_FLAG = 'Y'
where la.PERSON_ID = c_emp.person_id ;
else
update ifl_leave_absence la
set la.STATUS_FLAG = ''
where la.PERSON_ID = c_emp.person_id ;
end if;
EXCEPTION
WHEN OTHERS
THEN
error_msg := SUBSTR (SQLERRM, 1, 99);
DBMS_OUTPUT.put_line ('<<<<<<<<<< Exception>>>>>>>>>');
DBMS_OUTPUT.put_line ('person_id & Absence Date :' || c_emp.person_id ||' Start Date '||c_emp.absence_start_date||'End Date'||c_emp.absence_end_date);
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< Exception >>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,
error_msg||' '||'person_id & Absence Date :' || c_emp.person_id ||' Start Date '||c_emp.absence_start_date||'End Date'||c_emp.absence_end_date);
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< End >>>>>>>>>');
END;
END LOOP;
close c_sr;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('<<<<<<<<<< absence_upload sanwitch rule >>>>>>>>>');
DBMS_OUTPUT.put_line ('<<<<<<<<<<'' Sanwitch rule' || SQLERRM || '>>>>>>>>>'
);
DBMS_OUTPUT.put_line ('<<<<<<<<<<absence_upload>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< absence_upload sanwitch rule >>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,
'<<<<<<<<<<' || SQLERRM || '>>>>>>>>>'
);
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< absence_upload sanwitch rule >>>>>>>>>');
END;
procedure process_sshr_absence
( p_tkp NUMBER,
p_date_from VARCHAR2,
p_date_to VARCHAR2,
p_user_id NUMBER
) as
---------------------- API Out Parameters -------------
p_absence_days NUMBER;
p_absence_hours NUMBER;
p_absence_attendance_id NUMBER;
p_object_version_number NUMBER;
p_occurrence NUMBER;
p_dur_dys_less_warning BOOLEAN;
p_dur_hrs_less_warning BOOLEAN;
p_exceeds_pto_entit_warning BOOLEAN;
p_exceeds_run_total_warning BOOLEAN;
p_abs_overlap_warning BOOLEAN;
p_abs_day_after_warning BOOLEAN;
p_dur_overwritten_warning BOOLEAN;
p_days NUMBER;
p_days_hours NUMBER;
absance_type_name VARCHAR2 (50);
absance_id NUMBER;
v_gazet_days DATE;
v_rest_days DATE;
v_type VARCHAR2 (30);
last_present DATE;
v_absence_type VARCHAR2 (50);
v_net_value NUMBER (15, 2);
p_payroll NUMBER;
p_assignment_id NUMBER;
v_leave_type NUMBER;
v_absenc_id NUMBER;
error_msg VARCHAR2 (500);
v_start_date DATE;
v_end_date DATE;
v_days NUMBER;
v_date_from DATE
:= fnd_conc_date.string_to_date (p_date_from);
v_date_to DATE
:= fnd_conc_date.string_to_date (p_date_to);
--TO_CHAR(fnd_conc_date.string_to_date (p_work_start_date),'DD-MON-YYYY');
p_group NUMBER := p_tkp;
cursor ssh is
select sr.PERSON_ID,
sr.START_DATE,
sr.END_DATE,
sr.DAYS,
sr.ABSENC_ID,
0 business_group_id
from
ifl_absence_sw_rule sr
where sr.ABSENT_DATE between v_date_from and v_date_to
and sr.STATUS_FLAG is null
order by sr.ABSENT_DATE asc;
c_ss ssh%rowtype;
begin
emp_sandwitch_absence_process (p_tkp,p_date_from,p_date_to,p_user_id);
open ssh;
loop
fetch ssh into c_ss;
exit when ssh%notfound;
begin
hr_person_absence_api.create_person_absence (
p_validate => FALSE,
p_effective_date => c_ss.start_Date, --i.absence_start_date,
p_person_id => c_ss.person_id,
p_business_group_id => c_ss.business_group_id,
p_absence_attendance_type_id => c_ss.ABSENC_ID, ---i.absance_id,
p_date_start => c_ss.start_Date, --i.absence_start_date,
p_date_end => c_ss.end_Date, --i.absence_end_date,
-- p_attribute1 => i.PROCESS_FLAG,
p_absence_days => c_ss.days, --p_days,
p_absence_hours => p_days_hours,
p_absence_attendance_id => p_absence_attendance_id,
p_object_version_number => p_object_version_number,
p_occurrence => p_occurrence,
p_dur_dys_less_warning => p_dur_dys_less_warning,
p_dur_hrs_less_warning => p_dur_hrs_less_warning,
p_exceeds_pto_entit_warning => p_exceeds_pto_entit_warning,
p_exceeds_run_total_warning => p_exceeds_run_total_warning,
p_abs_overlap_warning => p_abs_overlap_warning,
p_abs_day_after_warning => p_abs_day_after_warning,
p_dur_overwritten_warning => p_dur_overwritten_warning);
if p_absence_attendance_id is not null then
update ifl_absence_sw_rule la
set la.STATUS_FLAG = 'Y'
where la.PERSON_ID = c_ss.person_id ;
else
update ifl_absence_sw_rule la
set la.STATUS_FLAG = ''
where la.PERSON_ID = c_ss.person_id ;
end if;
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
DBMS_OUTPUT.put_line ('attendance person_id :' || c_ss.person_id);
DBMS_OUTPUT.put_line ('<<<<<<<<<<>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<<>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, 'SSHR Absence Upload Person_id :' || c_ss.person_id);
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<<>>>>>>>>>');
p_absence_days := NULL;
p_absence_hours := NULL;
p_absence_attendance_id := NULL;
p_object_version_number := NULL;
p_occurrence := NULL;
p_dur_dys_less_warning := NULL;
p_dur_hrs_less_warning := NULL;
p_exceeds_pto_entit_warning := NULL;
p_exceeds_run_total_warning := NULL;
p_abs_overlap_warning := NULL;
p_abs_day_after_warning := NULL;
p_dur_overwritten_warning := NULL;
EXCEPTION
WHEN OTHERS
THEN
p_absence_days := NULL;
p_absence_hours := NULL;
p_absence_attendance_id := NULL;
p_object_version_number := NULL;
p_occurrence := NULL;
p_dur_dys_less_warning := NULL;
p_dur_hrs_less_warning := NULL;
p_exceeds_pto_entit_warning := NULL;
p_exceeds_run_total_warning := NULL;
p_abs_overlap_warning := NULL;
p_abs_day_after_warning := NULL;
p_dur_overwritten_warning := NULL;
error_msg := SUBSTR (SQLERRM, 1, 99);
DBMS_OUTPUT.put_line ('<<<<<<<<<<absence_upload>>>>>>>>>');
DBMS_OUTPUT.put_line ('<<<<<<<<<<' || SQLERRM || '>>>>>>>>>');
DBMS_OUTPUT.put_line ('<<<<<<<<<<absence_upload>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,
'<<<<<<<<<< SSHR Absence Upload >>>>>>>>>'
);
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<<' || SQLERRM || '>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,
'<<<<<<<<<< SSHR Absence Upload >>>>>>>>>');
END;
END LOOP;
close ssh;
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('<<<<<<<<<<absence_upload>>>>>>>>>');
DBMS_OUTPUT.put_line ('<<<<<<<<<<''TANVEER' || SQLERRM || '>>>>>>>>>'
);
DBMS_OUTPUT.put_line ('<<<<<<<<<< SSHR Absence Upload >>>>>>>>>');
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<<absence_upload>>>>>>>>>');
fnd_file.put_line (fnd_file.LOG,
'<<<<<<<<<<' || SQLERRM || '>>>>>>>>>'
);
fnd_file.put_line (fnd_file.LOG, '<<<<<<<<<< SSHR Absence Upload >>>>>>>>>');
END;
END;
/
------------------------ custom tables ---------------------------------------------------------------
- IFL_LEAVE_ABSENCE
- IFL_ABSENCE_SW_RULE
CREATE TABLE IFL_LEAVE_ABSENCE
(
PERSON_ID NUMBER,
ABSENT_DATE DATE,
LAST_PRESENT DATE,
GAZET_DAYS DATE,
REST_DAYS DATE,
TYPE VARCHAR2(50 BYTE),
LEAVE_TYPE NUMBER,
ABSENCE_TYPE VARCHAR2(100 BYTE),
ABSENC_ID NUMBER,
DAYS NUMBER,
START_DATE DATE,
END_DATE DATE,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER(15),
LAST_UPDATE_LOGIN NUMBER(15),
CREATED_BY NUMBER(15),
CREATION_DATE DATE,
STATUS_FLAG CHAR(2 CHAR)
)
-------------------------------------------------------------------------------------------------------------------------
CREATE TABLE IFL_ABSENCE_SW_RULE
(
PERSON_ID NUMBER,
ABSENT_DATE DATE,
LAST_PRESENT DATE,
GAZET_DAYS DATE,
REST_DAYS DATE,
TYPE VARCHAR2(50 BYTE),
LEAVE_TYPE NUMBER,
ABSENCE_TYPE VARCHAR2(100 BYTE),
ABSENC_ID NUMBER,
DAYS NUMBER,
START_DATE DATE,
END_DATE DATE,
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER(15),
LAST_UPDATE_LOGIN NUMBER(15),
CREATED_BY NUMBER(15),
CREATION_DATE DATE,
STATUS_FLAG CHAR(2 CHAR)
)
No comments:
Post a Comment