CREATE OR REPLACE PACKAGE APPS.TAD_RFID_DOWNLOAD IS
-- Author : CGH
-- Created : 27-NOV-2005
-- Purpose : For DOWNLOADING ATTENDANCE FROM RFID MACHINES
--Package Level Global Variables
CALENDOR_ID_G TAD_CALENDARDETAIL.CALENDARID%TYPE;
TYPE TPYE_EMP_RECORD IS RECORD(
EMPLOYEEID EMPMASTERINFO.EMPLOYEEID%TYPE,
SHIFT EMPMASTERINFO.SHIFT%TYPE,
DESIGNATIONID EMPMASTERINFO.DESIGNATIONID%TYPE,
RESTDAY01 EMPMASTERINFO.RESTDAY01%TYPE,
RESTDAY02 EMPMASTERINFO.RESTDAY02%TYPE,
LOCATIONID EMPMASTERINFO.LOCATIONID%TYPE,
MAINID EMPMASTERINFO.MAINID%TYPE,
EMPCAEGORY EMPMASTERINFO.EMPCATEGORY%TYPE,
PAYGROUPID EMPMASTERINFO.PAYGROUPID%TYPE,
GRADEID EMPMASTERINFO.GRADEID%TYPE,
OT_ALLOWED EMPMASTERINFO.OT_ALLOWED%TYPE,
CPL_ALLOWED EMPMASTERINFO.CPL_ALLOWED%TYPE,
GHL_ALLOWED EMPMASTERINFO.GHL_ALLOWED%TYPE,
ATD_WAIVEOFF EMPMASTERINFO.ATD_WAIVEOFF%TYPE,
CARDNO EMPMASTERINFO.CARDNO%TYPE,
CARD_ISSUE_DATE EMPMASTERINFO.CARD_ISSUE_DATE%TYPE,
EMPTYPE EMPMASTERINFO.EMPTYPE%TYPE,
GROSS_SALARY EMPMASTERINFO.GROSS_SALARY%TYPE);
TYPE TYPE_CALENDER_RECORD IS RECORD(
CALENDERID TAD_CALENDARDETAIL.CALENDARID%TYPE,
CLOCKIN TAD_CALENDARDETAIL.CLOCK_IN%TYPE,
CLOCKOUT TAD_CALENDARDETAIL.CLOCK_OUT%TYPE,
MAXCLOCKINTIME TAD_CALENDARDETAIL.MAXCLOCKINTIME%TYPE,
MINCLOCKOUTTIME TAD_CALENDARDETAIL.MINCLOCKOUTTIME%TYPE,
MINCLOCKINTIME TAD_CALENDARDETAIL.MINCLOCKINTIME%TYPE,
MAXCLOCKOUTTIME TAD_CALENDARDETAIL.MAXCLOCKOUTTIME%TYPE,
SHIFT varchar2(100) , --TAD_SHIFTROTATION.SHIFT%TYPE,
HOURS NUMBER(5, 2));
V_ATTEN_CLOCK_IN DATE;
V_ATTEN_CLOCK_OUT DATE;
V_CLOCKIN_CHANGE_FLAG TAD_REGISTER.CLOCKINCHANGE%TYPE := '0';
V_CLOCKOUT_CHANGE_FLAG TAD_REGISTER.CLOCKOUTCHANGE%TYPE := '0';
PROCEDURE DOWNLOAD_RFID_ATTENDANCE(V_CRITERIAID number,
V_USERID varchar2, --HXC_TK_GROUPS.TK_GROUP_ID%TYPE,
V_DATE DATE,
V_MESSAGE OUT VARCHAR2);
PROCEDURE GET_ATTENDANCE(V_USERID HXC_TK_GROUPS.TK_GROUP_ID%TYPE);
PROCEDURE GET_ATTENDANCE_EMPWISE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE,
V_USERID HXC_TK_GROUPS.TK_GROUP_ID%TYPE);
PROCEDURE PROCESS_ATTENDANCE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE,
V_USERID varchar2, ---- TIME KEEPER
V_DATE DATE);
FUNCTION GET_LAST_PROCESS_DATE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE,
V_PAYGROUPID EMPMASTERINFO.PAYGROUPID%TYPE,
V_ATDWAIVEOFF EMPMASTERINFO.ATD_WAIVEOFF%TYPE)
RETURN DATE;
/* FUNCTION GET_LAST_CHECKIN_DATE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE)
RETURN DATE;*/
FUNCTION IS_GH(V_DATE DATE) RETURN BOOLEAN;
FUNCTION GET_CALENDER(V_EMP_RECORD TPYE_EMP_RECORD, V_DATE DATE)
RETURN TYPE_CALENDER_RECORD;
FUNCTION GET_ATTENDANCE_TYPE(P_ATTENDANCE_FOUND BOOLEAN,
P_REST BOOLEAN,
P_GH BOOLEAN,
V_EMP_RECORD TPYE_EMP_RECORD)
RETURN VARCHAR2;
PROCEDURE GET_CLOCK_INOUT(V_ATTENDANCE_DATE DATE,
V_EMP_RECORD TPYE_EMP_RECORD,
V_CALENDER_RECORD TYPE_CALENDER_RECORD,
V_GH_FLAG BOOLEAN,
V_REST_FLAG BOOLEAN);
FUNCTION GET_CLOCK_TIME(P_CLOCK_DATE VARCHAR2, P_TIME VARCHAR2) RETURN DATE;
FUNCTION GET_MIN_MAX_DATE(V_CALENDER_RECORD TYPE_CALENDER_RECORD,
V_ATTENDANCE_DATE DATE,
V_EMP_RECORD TPYE_EMP_RECORD,
V_MIN_MAX_DATE CHAR) RETURN DATE;
FUNCTION GET_STD_CLOCK_IN_OUT(V_CLOCK_IN DATE,
V_CLOCK_OUT DATE,
V_ATTENDANCE_DATE DATE,
V_CLOCK_IN_OUT_FLAG CHAR) RETURN DATE;
FUNCTION IS_LOCATION_ACTIVE(V_LOCATIONID VARCHAR2)
RETURN NUMBER;
END TAD_RFID_DOWNLOAD;
/
-- Author : CGH
-- Created : 27-NOV-2005
-- Purpose : For DOWNLOADING ATTENDANCE FROM RFID MACHINES
--Package Level Global Variables
CALENDOR_ID_G TAD_CALENDARDETAIL.CALENDARID%TYPE;
TYPE TPYE_EMP_RECORD IS RECORD(
EMPLOYEEID EMPMASTERINFO.EMPLOYEEID%TYPE,
SHIFT EMPMASTERINFO.SHIFT%TYPE,
DESIGNATIONID EMPMASTERINFO.DESIGNATIONID%TYPE,
RESTDAY01 EMPMASTERINFO.RESTDAY01%TYPE,
RESTDAY02 EMPMASTERINFO.RESTDAY02%TYPE,
LOCATIONID EMPMASTERINFO.LOCATIONID%TYPE,
MAINID EMPMASTERINFO.MAINID%TYPE,
EMPCAEGORY EMPMASTERINFO.EMPCATEGORY%TYPE,
PAYGROUPID EMPMASTERINFO.PAYGROUPID%TYPE,
GRADEID EMPMASTERINFO.GRADEID%TYPE,
OT_ALLOWED EMPMASTERINFO.OT_ALLOWED%TYPE,
CPL_ALLOWED EMPMASTERINFO.CPL_ALLOWED%TYPE,
GHL_ALLOWED EMPMASTERINFO.GHL_ALLOWED%TYPE,
ATD_WAIVEOFF EMPMASTERINFO.ATD_WAIVEOFF%TYPE,
CARDNO EMPMASTERINFO.CARDNO%TYPE,
CARD_ISSUE_DATE EMPMASTERINFO.CARD_ISSUE_DATE%TYPE,
EMPTYPE EMPMASTERINFO.EMPTYPE%TYPE,
GROSS_SALARY EMPMASTERINFO.GROSS_SALARY%TYPE);
TYPE TYPE_CALENDER_RECORD IS RECORD(
CALENDERID TAD_CALENDARDETAIL.CALENDARID%TYPE,
CLOCKIN TAD_CALENDARDETAIL.CLOCK_IN%TYPE,
CLOCKOUT TAD_CALENDARDETAIL.CLOCK_OUT%TYPE,
MAXCLOCKINTIME TAD_CALENDARDETAIL.MAXCLOCKINTIME%TYPE,
MINCLOCKOUTTIME TAD_CALENDARDETAIL.MINCLOCKOUTTIME%TYPE,
MINCLOCKINTIME TAD_CALENDARDETAIL.MINCLOCKINTIME%TYPE,
MAXCLOCKOUTTIME TAD_CALENDARDETAIL.MAXCLOCKOUTTIME%TYPE,
SHIFT varchar2(100) , --TAD_SHIFTROTATION.SHIFT%TYPE,
HOURS NUMBER(5, 2));
V_ATTEN_CLOCK_IN DATE;
V_ATTEN_CLOCK_OUT DATE;
V_CLOCKIN_CHANGE_FLAG TAD_REGISTER.CLOCKINCHANGE%TYPE := '0';
V_CLOCKOUT_CHANGE_FLAG TAD_REGISTER.CLOCKOUTCHANGE%TYPE := '0';
PROCEDURE DOWNLOAD_RFID_ATTENDANCE(V_CRITERIAID number,
V_USERID varchar2, --HXC_TK_GROUPS.TK_GROUP_ID%TYPE,
V_DATE DATE,
V_MESSAGE OUT VARCHAR2);
PROCEDURE GET_ATTENDANCE(V_USERID HXC_TK_GROUPS.TK_GROUP_ID%TYPE);
PROCEDURE GET_ATTENDANCE_EMPWISE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE,
V_USERID HXC_TK_GROUPS.TK_GROUP_ID%TYPE);
PROCEDURE PROCESS_ATTENDANCE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE,
V_USERID varchar2, ---- TIME KEEPER
V_DATE DATE);
FUNCTION GET_LAST_PROCESS_DATE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE,
V_PAYGROUPID EMPMASTERINFO.PAYGROUPID%TYPE,
V_ATDWAIVEOFF EMPMASTERINFO.ATD_WAIVEOFF%TYPE)
RETURN DATE;
/* FUNCTION GET_LAST_CHECKIN_DATE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE)
RETURN DATE;*/
FUNCTION IS_GH(V_DATE DATE) RETURN BOOLEAN;
FUNCTION GET_CALENDER(V_EMP_RECORD TPYE_EMP_RECORD, V_DATE DATE)
RETURN TYPE_CALENDER_RECORD;
FUNCTION GET_ATTENDANCE_TYPE(P_ATTENDANCE_FOUND BOOLEAN,
P_REST BOOLEAN,
P_GH BOOLEAN,
V_EMP_RECORD TPYE_EMP_RECORD)
RETURN VARCHAR2;
PROCEDURE GET_CLOCK_INOUT(V_ATTENDANCE_DATE DATE,
V_EMP_RECORD TPYE_EMP_RECORD,
V_CALENDER_RECORD TYPE_CALENDER_RECORD,
V_GH_FLAG BOOLEAN,
V_REST_FLAG BOOLEAN);
FUNCTION GET_CLOCK_TIME(P_CLOCK_DATE VARCHAR2, P_TIME VARCHAR2) RETURN DATE;
FUNCTION GET_MIN_MAX_DATE(V_CALENDER_RECORD TYPE_CALENDER_RECORD,
V_ATTENDANCE_DATE DATE,
V_EMP_RECORD TPYE_EMP_RECORD,
V_MIN_MAX_DATE CHAR) RETURN DATE;
FUNCTION GET_STD_CLOCK_IN_OUT(V_CLOCK_IN DATE,
V_CLOCK_OUT DATE,
V_ATTENDANCE_DATE DATE,
V_CLOCK_IN_OUT_FLAG CHAR) RETURN DATE;
FUNCTION IS_LOCATION_ACTIVE(V_LOCATIONID VARCHAR2)
RETURN NUMBER;
END TAD_RFID_DOWNLOAD;
/
CREATE OR REPLACE PACKAGE BODY APPS.TAD_RFID_DOWNLOAD IS
PROCEDURE DOWNLOAD_RFID_ATTENDANCE(V_CRITERIAID number,
V_USERID varchar2, -- HXC_TK_GROUPS.TK_GROUP_ID%TYPE,
V_DATE DATE,
V_MESSAGE OUT VARCHAR2)
IS
TYPE TABLE_EMPID IS TABLE OF EMPMASTERINFO.EMPLOYEEID%TYPE;
V_EMPID TABLE_EMPID;
----V_PROCESSID HCMPROCESSDETAIL.PROCESSID%TYPE := CMN_UPDATEPROCESSINFO_SEQ.NEXTVAL; -------NEED TO BE CHANGED
BEGIN
/*IF V_DATE > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20001,
'Could Not Down Load Attendance of Date: ' ||
V_DATE);
END IF;*/
/*
CMN_UPDATEPROCESSINFO_START('TAD_RFID_DOWNLOAD.DOWNLOAD_RFID_ATTENDANCE', --------------- NEED TO DISCUSS
V_USERID,
V_CRITERIAID,
'',
V_PROCESSID);
HCM_REPORTS_PKG.APPLY_WF_FILTER_CRITERIA(V_CRITERIAID, V_USERID); ------------- NEED TO DISCUSS
*/
--1. Download Attendance from RFID Schema of Employee
--GET_ATTENDANCE(V_USERID);
/*
SELECT E.EMPLOYEEID BULK COLLECT
INTO V_EMPID
FROM ifl.SEC_EMPLOYEES E, EMPMASTERINFO S -------------- SEC_EMPLOYEES need to be discussed
WHERE E.MISFLAG = 1
AND E.USERID = V_USERID
AND S.EMPLOYEEID = E.EMPLOYEEID
AND S.STATUS = 0
AND S.joiningdate <= V_DATE;
*/
SELECT distinct S.EMPLOYEEID BULK COLLECT INTO V_EMPID
-- htg.TK_GROUP_ID
FROM EMPMASTERINFO S ,
hxt_add_assign_info_f hasif,
per_all_assignments_f paaf,
per_all_people_f papf,
hxt_earning_policies hep,
hxc_tk_groups htg ,
hxc_tk_group_queries htgq ,
hxc_tk_group_query_criteria htgqc
WHERE 1 = 1
AND s.employeeid = papf.PERSON_ID
AND papf.person_id = paaf.person_id
AND hasif.assignment_id = paaf.assignment_id
AND hep.ID = hasif.earning_policy
AND papf.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 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 htg.TK_GROUP_ID = V_CRITERIAID
---and papf.PERSON_ID in(9384,9383)
AND S.joiningdate <= V_DATE;
-- AND papf.person_id = :p_person_id
IF V_EMPID.COUNT > 0 ---AND V_EMPID.COUNT IS NOT NULL commnet by 23 sep 2016
THEN
DBMS_OUTPUT.PUT_LINE ('IF V_EMPID.COUNT > 0 AND V_EMPID.COUNT IS NOT NULL THEN');
FOR I IN V_EMPID.FIRST .. V_EMPID.LAST LOOP
DBMS_OUTPUT.PUT_LINE ('FOR I IN V_EMPID.FIRST .. V_EMPID.LAST LOOP');
DBMS_OUTPUT.PUT_LINE ('BEFORE PROCESS_ATTENDANCE(V_EMPID(I), V_USERID, V_DATE);'||V_EMPID(I)||'::'|| V_USERID||'::'|| V_DATE);
PROCESS_ATTENDANCE(V_EMPID(I), V_USERID, V_DATE);
DBMS_OUTPUT.PUT_LINE ('AFTER PROCESS_ATTENDANCE(V_EMPID(I), V_USERID, V_DATE);'||V_EMPID(I)||'::'|| V_USERID||'::'|| V_DATE);
END LOOP;
END IF;
-- CMN_UPDATEPROCESSINFO_END(V_PROCESSID); ------ NEED TO COMPILE PROCEDURE
COMMIT;
V_MESSAGE := 'Downloaded Successfully';
EXCEPTION
WHEN OTHERS THEN
V_MESSAGE := SQLERRM||':::::: RFID Exception';
ROLLBACK;
SEC_ERR_LOG(SQLCODE,
SQLERRM,
'TAD_RFID_DOWNLOAD.DOWNLOAD_RFID_ATTENDANCE',
V_USERID,
'');
-- CMN_UPDATEPROCESSINFO_ERROR(V_PROCESSID, SQLCODE, SQLERRM); ------ NEED TO COMPILE PROCEDURE
COMMIT;
END;
--*****************************************************************
--Download Attendance from RFID Schema
/*
Purpose:
FETCH Attendence Data FROM RFID Machine Schema
INTO HCM Schema FOR Processing
Parameters:
P_1: V_EMPID THE Employee Whose Attendance we are going TO FETCH
*/
PROCEDURE GET_ATTENDANCE(V_USERID HXC_TK_GROUPS.TK_GROUP_ID%TYPE) IS
V_CONDITION VARCHAR2(2000);
BEGIN
-- Fetching Data from Previous Month Table
V_CONDITION := 'INSERT INTO TAD_EMPATTENDANCELOG(EMPLOYEEID, CARDNO, SWAPDATETIME, STATUS, ENTEREDBY,MACHINE_NO)' ||
' SELECT DISTINCT E.EMPLOYEEID, T.CARDNO,TAD_RFID_DOWNLOAD.GET_CLOCK_TIME(T.SWIP_DATE, T.SWIP_TIME),0,' ||
CHR(39) || V_USERID || CHR(39) || ',T.RDR_CODE FROM ' ||
' SAMS.M' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyymm') ||
' T, TAD_RFIDEMPCARDS E, SEC_EMPLOYEES S, EMPMASTERINFO M' ||
' WHERE E.CARDNO = T.CARDNO AND M.EMPLOYEEID = S.EMPLOYEEID AND ' ||
' M.EMP_STATUS = 0 AND S.EMPLOYEEID = E.EMPLOYEEID AND S.USERID = ' ||
CHR(39) || V_USERID || CHR(39) || ' AND S.MISFLAG = 1 ' ||
' AND TO_NUMBER(SUBSTR(T.SWIP_DATE, 7, 2)) > 28 ' ||
' AND (TAD_RFID_DOWNLOAD.GET_CLOCK_TIME(T.SWIP_DATE, T.SWIP_TIME),E.EMPLOYEEID,E.CARDNO) NOT IN' ||
' (SELECT DISTINCT V.SWAPDATETIME,V.EMPLOYEEID,V.CARDNO FROM TAD_EMPATTENDANCELOG V ' ||
' WHERE V.EMPLOYEEID = E.EMPLOYEEID AND V.EMPLOYEEID = S.EMPLOYEEID)' ||
' UNION ALL ' ||
' SELECT DISTINCT E.EMPLOYEEID, T.CARDNO,TAD_RFID_DOWNLOAD.GET_CLOCK_TIME(T.SWIP_DATE, T.SWIP_TIME),0,' ||
CHR(39) || V_USERID || CHR(39) || ',T.RDR_CODE FROM ' ||
' SAMS.M' || TO_CHAR(SYSDATE, 'yyyymm') ||
' T, TAD_RFIDEMPCARDS E, SEC_EMPLOYEES S, EMPMASTERINFO M' ||
' WHERE E.CARDNO = T.CARDNO AND M.EMPLOYEEID = S.EMPLOYEEID AND ' ||
' M.EMP_STATUS = 0 AND S.EMPLOYEEID = E.EMPLOYEEID AND S.USERID = ' ||
CHR(39) || V_USERID || CHR(39) || ' AND S.MISFLAG = 1 ' ||
' AND (TAD_RFID_DOWNLOAD.GET_CLOCK_TIME(T.SWIP_DATE, T.SWIP_TIME),E.EMPLOYEEID,E.CARDNO) NOT IN' ||
' (SELECT DISTINCT V.SWAPDATETIME,V.EMPLOYEEID,V.CARDNO FROM TAD_EMPATTENDANCELOG V ' ||
' WHERE V.EMPLOYEEID = E.EMPLOYEEID AND V.EMPLOYEEID = S.EMPLOYEEID)';
EXECUTE IMMEDIATE V_CONDITION;
EXCEPTION
WHEN OTHERS THEN
SEC_ERR_LOG(SQLCODE,
SQLERRM,
'TAD_RFID_DOWNLOAD.GET_ATTENDANCE',
V_USERID,
'');
COMMIT;
RAISE;
END;
PROCEDURE GET_ATTENDANCE_EMPWISE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE,
V_USERID HXC_TK_GROUPS.TK_GROUP_ID%TYPE) IS
V_CONDITION VARCHAR2(1000);
BEGIN
-- Fetching Data from Previous Month Table
V_CONDITION := 'INSERT INTO TAD_EMPATTENDANCELOG(EMPLOYEEID, CARDNO, SWAPDATETIME, STATUS, ENTEREDBY,MACHINE_NO)' ||
' SELECT E.EMPLOYEEID, T.CARDNO,TAD_RFID_DOWNLOAD.GET_CLOCK_TIME(T.SWIP_DATE, T.SWIP_TIME),0,' ||
CHR(39) || V_USERID || CHR(39) || ',T.RDR_CODE FROM ' ||
' SAMS.M' || TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'yyyymm') ||
' T, TAD_RFIDEMPCARDS E' ||
' WHERE E.CARDNO = T.CARDNO AND E.EMPLOYEEID = ' ||
V_EMPID ||
' AND TO_NUMBER(SUBSTR(T.SWIP_DATE, 7, 2)) > 25 ' ||
' AND TAD_RFID_DOWNLOAD.GET_CLOCK_TIME(T.SWIP_DATE, T.SWIP_TIME) NOT IN' ||
' (SELECT DISTINCT V.SWAPDATETIME FROM TAD_EMPATTENDANCELOG V ' ||
' WHERE V.EMPLOYEEID = E.EMPLOYEEID AND V.EMPLOYEEID = ' ||
V_EMPID || ')';
EXECUTE IMMEDIATE V_CONDITION;
-- Fetching Data from Current Month Table
V_CONDITION := 'INSERT INTO TAD_EMPATTENDANCELOG(EMPLOYEEID, CARDNO, SWAPDATETIME, STATUS, ENTEREDBY,MACHINE_NO)' ||
' SELECT E.EMPLOYEEID, T.CARDNO,TAD_RFID_DOWNLOAD.GET_CLOCK_TIME(T.SWIP_DATE, T.SWIP_TIME),0,' ||
CHR(39) || V_USERID || CHR(39) || ',T.RDR_CODE FROM ' ||
' SAMS.M' || TO_CHAR(SYSDATE, 'yyyymm') ||
' T, TAD_RFIDEMPCARDS E' ||
' WHERE E.CARDNO = T.CARDNO AND E.EMPLOYEEID = ' ||
V_EMPID ||
' AND TAD_RFID_DOWNLOAD.GET_CLOCK_TIME(T.SWIP_DATE, T.SWIP_TIME) NOT IN' ||
' (SELECT DISTINCT V.SWAPDATETIME FROM TAD_EMPATTENDANCELOG V ' ||
' WHERE V.EMPLOYEEID = E.EMPLOYEEID AND V.EMPLOYEEID = ' ||
V_EMPID || ')';
EXECUTE IMMEDIATE V_CONDITION;
--COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
--*****************************************************************
--Get Last Process Date
/*
Purpose:
This Function get the Last Attendance Process Date for
selected employee
Parameters:
P_1: V_EMPID We will get Last Process Date of this Employee
*/
FUNCTION GET_LAST_PROCESS_DATE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE,
V_PAYGROUPID EMPMASTERINFO.PAYGROUPID%TYPE,
V_ATDWAIVEOFF EMPMASTERINFO.ATD_WAIVEOFF%TYPE)
RETURN DATE IS
V_LAST_PROCESS_DATE DATE;
V_ATT_MONTH_START_DATE DATE;
BEGIN
BEGIN
SELECT MAX(DECODE(TRUNC(T.STATUS), '0',
----------------------- COMMENTED ON 18TH AUG 2016 -----------------------
-- TO_DATE(T.ATTENDANCEDATE, 'dd-MON-yy'),
-- TO_DATE(T.ATTENDANCEDATE, 'dd-MON-yy') + 1))
T.ATTENDANCEDATE,
T.ATTENDANCEDATE + 1))
INTO V_LAST_PROCESS_DATE
FROM TAD_REGISTER T
WHERE T.EMPLOYEEID = V_EMPID;
---exception when no_data_found then
---V_LAST_PROCESS_DATE := fnd_conc_date.STRING_TO_DATE('01-AUG-2001');
END;
IF V_ATDWAIVEOFF = '1' THEN
BEGIN
SELECT
------------------- MAX(TO_date(T.ATTENDANCEDATE, 'dd-MON-YY')) COMMENTED ON 17th AUG 2016
MAX(T.ATTENDANCEDATE)
INTO V_LAST_PROCESS_DATE
FROM TAD_REGISTER T
WHERE T.EMPLOYEEID = V_EMPID;
---exception when no_data_found then
---V_LAST_PROCESS_DATE := fnd_conc_date.STRING_TO_DATE('01-AUG-2001');
END;
END IF;
/*
SELECT P.FROMDATE
INTO V_ATT_MONTH_START_DATE
FROM PAYGROUPS P
WHERE P.PAYGROUPID = V_PAYGROUPID;
*/
BEGIN
/*select max(ppa.DATE_EARNED)+1 --- change by end_date into date_earned 18 aug 2016
INTO V_ATT_MONTH_START_DATE
from
per_time_periods ptp ,
pay_payroll_actions ppa,
pay_assignment_actions paa ,
per_assignments_x pax
, pay_payrolls_f ppf
where 1=1
and ptp.TIME_PERIOD_ID = ppa.TIME_PERIOD_ID
and ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
and paa.ASSIGNMENT_ID = pax.ASSIGNMENT_ID
and ptp.PERIOD_TYPE like 'Calendar Month'
and ptp.STATUS = 'O'
and ppf.PAYROLL_ID = pax.PAYROLL_ID
and pax.PRIMARY_FLAG = 'Y'
and pax.PERSON_ID = V_EMPID
and ppa.ACTION_STATUS = 'C';*/ ---------------------- change by tanveer 02 sep 2016
select trunc(min(te.SWAPDATETIME)) into V_ATT_MONTH_START_DATE
from TAD_EMPATTENDANCELOG te
where 1=1
and te.EMPLOYEEID = V_EMPID
and te.STATUS = '0';
---exception when no_data_found then
--- V_ATT_MONTH_START_DATE := fnd_conc_date.STRING_TO_DATE('01-AUG-2001');
END ;
IF V_LAST_PROCESS_DATE IS NULL OR
V_LAST_PROCESS_DATE < V_ATT_MONTH_START_DATE
or V_LAST_PROCESS_DATE = V_ATT_MONTH_START_DATE THEN -- add one more conditioin 23 sep 2016
V_LAST_PROCESS_DATE := V_ATT_MONTH_START_DATE;
END IF;
SELECT CASE
WHEN V_LAST_PROCESS_DATE - E.JOININGDATE < 0 THEN
E.JOININGDATE
ELSE
V_LAST_PROCESS_DATE
END
INTO V_LAST_PROCESS_DATE
FROM EMPMASTERINFO E
WHERE E.EMPLOYEEID = V_EMPID;
RETURN V_LAST_PROCESS_DATE;
END;
--*****************************************************************
/*--Get Last Date
\*
Purpose:
This Function get the Last Day When
selected employee Swapped The Card
Parameters:
P_1: V_EMPID We will get Last Process Date of this Employee
*\
FUNCTION GET_LAST_CHECKIN_DATE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE)
RETURN DATE IS
--V_LAST_CHECKIN_DATE DATE;
BEGIN
\*SELECT DECODE(MAX(TO_DATE(T.SWAPDATETIME, 'dd-MON-yy')),
NULL,
SYSDATE,
MAX(TO_DATE(T.SWAPDATETIME, 'dd-MON-yy')))
INTO V_LAST_CHECKIN_DATE
FROM TAD_EMPATTENDANCELOG T
WHERE T.STATUS = 0 AND T.EMPLOYEEID = V_EMPID;
RETURN V_LAST_CHECKIN_DATE;*\
RETURN SYSDATE;
END;*/
--*****************************************************************
--Is Date Is GH Holiday
/*
Purpose:
This Function checks if the selected date is GH Holiday from
GH Register. Returns True if Date is GH Else False
Parameters:
P_1: V_DATE This date will be checked
*/
FUNCTION IS_GH(V_DATE DATE) RETURN BOOLEAN IS
V_GH_DATE DATE;
BEGIN
/*
SELECT D.GH_DATE
INTO V_GH_DATE
FROM TAD_GH_CALENDARDETAIL D
WHERE D.GH_DATE = TO_DATE(V_DATE)
AND D.GHCALENDARID =
(SELECT P.PARA_VALUE_1
FROM CMN_PARAMETERS P
WHERE P.PARAMETERID = 'GHCALENDER');
*/
RETURN FALSE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END;
--*****************************************************************
--Get Calender
/*
Purpose:
This Function Get Calender Record (Default In/OUt Time) for the
Selected Employee for Selected Date
Parameters:
P_1: V_EMP_RECORD Get Calender for this Employee
p_2: V_DATE Get Calender of Date
*/
FUNCTION GET_CALENDER(V_EMP_RECORD TPYE_EMP_RECORD, V_DATE DATE)
RETURN TYPE_CALENDER_RECORD IS
V_SHIFT_CALENDER_RECORD TYPE_CALENDER_RECORD;
V_IND_CALENDER_RECORD TYPE_CALENDER_RECORD;
BEGIN
-- Get Shift Level Calender
BEGIN
/*
SELECT DISTINCT D.CALENDARID,
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'0') AS CLOCKIN,
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'1') AS CLOCKOUT,
GET_STD_CLOCK_IN_OUT(D.MAXCLOCKINTIME,
D.CLOCK_OUT,
V_DATE,
'0'),
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.MINCLOCKOUTTIME,
V_DATE,
'1'),
GET_STD_CLOCK_IN_OUT(D.MINCLOCKINTIME,
D.CLOCK_OUT,
V_DATE,
'0') MINCLOCKINTIME,
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.MAXCLOCKOUTTIME,
V_DATE,
'1') MAXCLOCKOUTTIME,
T.SHIFT,
ROUND((GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'1') -
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'0')) * 24,
2)
INTO V_SHIFT_CALENDER_RECORD
FROM TAD_CALENDARDETAIL D
WHERE 1=1
ANDT.CALENDARID = D.CALENDARID
AND UPPER(D.DAY) = UPPER(TRIM(TO_CHAR(V_DATE, 'Day')))
AND T.LOCATIONID = V_EMP_RECORD.LOCATIONID
AND T.SHIFT = V_EMP_RECORD.SHIFT
AND TO_DATE(V_DATE, 'dd-MON-yy') BETWEEN TO_DATE(T.FROMDATE, 'dd-MON-yy') AND TO_DATE(T.TODATE, 'dd-MON-yy');
*/
---for i in V_EMP_RECORD.first .. V_EMP_RECORD.last loop
SELECT
DISTINCT D.CALENDARID,
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'0') AS CLOCKIN,
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'1') AS CLOCKOUT,
GET_STD_CLOCK_IN_OUT(D.MAXCLOCKINTIME,
D.CLOCK_OUT,
V_DATE,
'0'),
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.MINCLOCKOUTTIME,
V_DATE,
'1'),
GET_STD_CLOCK_IN_OUT(D.MINCLOCKINTIME,
D.CLOCK_OUT,
V_DATE,
'0') MINCLOCKINTIME,
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.MAXCLOCKOUTTIME,
V_DATE,
'1') MAXCLOCKOUTTIME,
D.SHIFT_ID,
ROUND((GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'1') -
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'0')) * 24,
2)
INTO V_SHIFT_CALENDER_RECORD
FROM TAD_CALENDARDETAIL d,
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 plandetail
hxt_shifts hs, --shifts
hxt_earning_policies hep
/*
, hxc_tk_groups htg ,
hxc_tk_group_queries htgq ,
hxc_tk_group_query_criteria htgqc
*/
WHERE 1 = 1
AND d.shift_id(+) = hs.id
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
---------------- ADDED ON 05 OCTOBER 2015 -----------------
/*
AND papf.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 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 hws.week_day = SUBSTR (TO_CHAR (V_DATE, 'DAY'), 1, 3
)
AND hrs.start_date =
(SELECT MAX (hrs1.start_date)
FROM hxt_rotation_schedules hrs1
WHERE hrs1.rtp_id = hrs.rtp_id
AND TO_CHAR (hrs1.start_date, 'DD-MON-YYYY') <= TRUNC (V_DATE))
AND V_DATE BETWEEN hasif.effective_start_date AND hasif.effective_end_date
-- AND papf.person_id = :p_person_id
AND papf.PERSON_ID = V_EMP_RECORD.employeeid
AND V_DATE between hasif.EFFECTIVE_START_DATE and hasif.EFFECTIVE_END_DATE;
dbms_output.put_line ('CALANDER: '||V_SHIFT_CALENDER_RECORD.CALENDERID);
-- end loop;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20006,
'No Calender Is Ative for Shift: ' ||
V_EMP_RECORD.SHIFT || ' Location: ' ||
V_EMP_RECORD.LOCATIONID || ' Date: ' ||
V_DATE);
WHEN TOO_MANY_ROWS THEN
RAISE_APPLICATION_ERROR(-20006,
'More Than One Calender Is Ative for Shift: ' ||
V_EMP_RECORD.SHIFT || ' Location: ' ||
V_EMP_RECORD.LOCATIONID || ' Date: ' ||
V_DATE ||
' . Delete One Calender and Run Process Again.');
END;
/*
-- Get Employee Level Calender
BEGIN
SELECT DISTINCT D.CALENDARID,
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'0') AS CLOCKIN,
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'1') AS CLOCKOUT,
GET_STD_CLOCK_IN_OUT(D.MAXCLOCKINTIME,
D.CLOCK_OUT,
V_DATE,
'0'),
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.MINCLOCKOUTTIME,
V_DATE,
'1'),
GET_STD_CLOCK_IN_OUT(D.MINCLOCKINTIME,
D.CLOCK_OUT,
V_DATE,
'0') MINCLOCKINTIME,
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.MAXCLOCKOUTTIME,
V_DATE,
'1') MAXCLOCKOUTTIME,
NVL(T.SHIFT, V_SHIFT_CALENDER_RECORD.SHIFT),
ROUND((GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'1') -
GET_STD_CLOCK_IN_OUT(D.CLOCK_IN,
D.CLOCK_OUT,
V_DATE,
'0')) * 24)
INTO V_SHIFT_CALENDER_RECORD
FROM TAD_INDIVIDUALROTATION T, TAD_CALENDARDETAIL D
WHERE T.CALENDARID = D.CALENDARID
AND UPPER(D.DAY) = UPPER(TRIM(TO_CHAR(V_DATE, 'Day')))
AND T.CALENDARID = D.CALENDARID
AND T.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND TO_DATE(V_DATE, 'dd-MON-yy') BETWEEN
TO_DATE(T.FROMDATE, 'dd-MON-yy') AND
TO_DATE(T.TODATE, 'dd-MON-yy');
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_IND_CALENDER_RECORD.CALENDERID := NULL;
V_IND_CALENDER_RECORD.CLOCKIN := NULL;
V_IND_CALENDER_RECORD.CLOCKOUT := NULL;
END;
*/
/* Check IF Individual (Employee Level) Calender is found than
V_IND_CALENDER_RECORD is Selected. If V_IND_CALENDER_RECORD is NULL
Than V_SHIFT_CALENDER_RECORD IS Selected*/
IF (V_IND_CALENDER_RECORD.CALENDERID IS NOT NULL) THEN
RETURN V_IND_CALENDER_RECORD;
ELSE
RETURN V_SHIFT_CALENDER_RECORD;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20006, 'No Calender Is Ative');
END;
--*****************************************************************
--Get Attendence Type
/*
Purpose:
This Function Returns the Attendance Type
Parameters:
P_1: P_ATTENDANCE_FOUND If True Employee has swapped the Card else
Employee has not Swapped the Card
p_2: P_REST If True Than The Date is Rest Day
p_3: P_GH If True Than The Date IS GH Holiday
*/
FUNCTION GET_ATTENDANCE_TYPE(P_ATTENDANCE_FOUND BOOLEAN,
P_REST BOOLEAN,
P_GH BOOLEAN,
V_EMP_RECORD TPYE_EMP_RECORD)
RETURN VARCHAR2 IS
BEGIN
--Not Found in RFID
IF P_ATTENDANCE_FOUND = FALSE THEN
IF P_GH = TRUE THEN
RETURN 'GH';
ELSIF P_REST = TRUE THEN
RETURN 'R';
ELSE
RETURN 'A';
END IF;
--Found in RFID
ELSE
IF P_GH = TRUE AND V_EMP_RECORD.GHL_ALLOWED = '1' THEN
IF FLOOR((V_ATTEN_CLOCK_OUT - V_ATTEN_CLOCK_IN) * 24) < 4 THEN
RETURN 'GH';
ELSE
RETURN 'OT';
END IF;
ELSIF P_GH = TRUE AND V_EMP_RECORD.GHL_ALLOWED = '0' THEN
RETURN 'GH';
ELSIF P_REST = TRUE AND V_EMP_RECORD.CPL_ALLOWED = '1' THEN
RETURN 'C';
ELSIF P_REST = TRUE AND V_EMP_RECORD.CPL_ALLOWED = '0' THEN
RETURN 'R';
ELSE
IF FLOOR((V_ATTEN_CLOCK_OUT - V_ATTEN_CLOCK_IN) * 24) < 4 THEN
RETURN 'A';
ELSE
RETURN 'P';
END IF;
END IF;
END IF;
END;
--*****************************************************************
--Get IN/Out Time
/*
Purpose:
Gets the Clock In and Clock Out Time of Selected date
Parameters:
P_1: V_ATTENDANCE_DATE Processing Date
p_2: V_EMP_RECORD Employee Record
p_3: V_CALENDER_RECORD Calender Record For Selected Date and Employee
*/
PROCEDURE GET_CLOCK_INOUT(V_ATTENDANCE_DATE DATE,
V_EMP_RECORD TPYE_EMP_RECORD,
V_CALENDER_RECORD TYPE_CALENDER_RECORD,
V_GH_FLAG BOOLEAN,
V_REST_FLAG BOOLEAN) IS
V_HRSIN NUMBER(2) := 0;
V_HRSOUT NUMBER(2) := 0;
V_MINDATE DATE;
V_MAXDATE DATE;
V_OT_APPROVED_WF NUMBER(2);
BEGIN
V_CLOCKIN_CHANGE_FLAG := '0';
V_CLOCKOUT_CHANGE_FLAG := '0';
V_MINDATE := GET_MIN_MAX_DATE(V_CALENDER_RECORD, V_ATTENDANCE_DATE, V_EMP_RECORD, 0);
V_MAXDATE := GET_MIN_MAX_DATE(V_CALENDER_RECORD, V_ATTENDANCE_DATE, V_EMP_RECORD, 1);
DBMS_OUTPUT.PUT_LINE ('attendance date: ' || V_ATTENDANCE_DATE);
DBMS_OUTPUT.PUT_LINE ('V_MINDATE:'||V_MINDATE);
DBMS_OUTPUT.PUT_LINE ('V_MINDATE:'||V_MAXDATE);
--DBMS_OUTPUT.PUT_LINE ('V_MINDATE: '||(TO_CHAR(V_MINDATE, 'dd-Mon-yyyy hh:mi am')));
--DBMS_OUTPUT.PUT_LINE ('V_MAXDATE: '||(TO_CHAR(V_MAXDATE, 'dd-Mon-yyyy hh:mi am')));
/*
SELECT COUNT(W.CPL_HOURS)
INTO V_OT_APPROVED_WF
FROM TAD_OT_CPL_WF W
WHERE W.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND W.CPL_DATE = V_ATTENDANCE_DATE
AND W.APPROVAL_STATUS = 'APPROVED';
*/
--Get Clock In/ Clock Out
SELECT MIN(T.SWAPDATETIME) AS CLOCKIN, MAX(T.SWAPDATETIME) AS CLOCKOUT
INTO V_ATTEN_CLOCK_IN, V_ATTEN_CLOCK_OUT
FROM TAD_EMPATTENDANCELOG T
WHERE T.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND T.SWAPDATETIME BETWEEN V_MINDATE /*V_CALENDER_RECORD.MINCLOCKINTIME*/
AND V_MAXDATE;
--DBMS_OUTPUT.PUT_LINE ('V_ATTEN_CLOCK_IN: '||(TO_CHAR(V_ATTEN_CLOCK_IN, 'dd-Mon-yyyy hh:mi am')));
--DBMS_OUTPUT.PUT_LINE ('V_ATTEN_CLOCK_OUT: '||(TO_CHAR(V_ATTEN_CLOCK_OUT, 'dd-Mon-yyyy hh:mi am')));
--If Card is Swapped Twice With in 15 Min
IF (V_ATTEN_CLOCK_OUT - V_ATTEN_CLOCK_IN) * 24 * 60 < 15 THEN
V_ATTEN_CLOCK_OUT := V_ATTEN_CLOCK_IN;
END IF;
-- If Both are Equal Than Employee has Swapped Card Once. We have to Check
-- That Swapped Time is In or Out
IF (TO_CHAR(V_ATTEN_CLOCK_IN, 'dd-Mon-yyyy hh:mi am') =
TO_CHAR(V_ATTEN_CLOCK_OUT, 'dd-Mon-yyyy hh:mi am')) THEN
V_HRSIN := ABS(TO_NUMBER(TO_CHAR(V_CALENDER_RECORD.CLOCKIN, 'hh24')) -
TO_NUMBER(TO_CHAR(V_ATTEN_CLOCK_IN, 'hh24')));
V_HRSOUT := ABS(TO_NUMBER(TO_CHAR(V_CALENDER_RECORD.CLOCKOUT, 'hh24')) -
TO_NUMBER(TO_CHAR(V_ATTEN_CLOCK_OUT, 'hh24')));
-- If Swapped Time is IN
IF V_HRSIN <= V_HRSOUT THEN
V_ATTEN_CLOCK_OUT := NULL;
-- If Swappe Time is OUT
ELSE
V_ATTEN_CLOCK_IN := NULL;
END IF;
IF
--------- COMMENTED ON 17TH AUGUST 2017 ----------
-----TO_DATE(V_ATTEN_CLOCK_IN, 'dd-Mon-yyyy') != TO_DATE(V_ATTENDANCE_DATE, 'dd-Mon-yyyy') THEN
trunc(V_ATTEN_CLOCK_IN) != V_ATTENDANCE_DATE THEN
V_ATTEN_CLOCK_OUT := V_ATTEN_CLOCK_IN;
V_ATTEN_CLOCK_IN := NULL;
END IF;
END IF;
--- If Employee Is not Issued RFID Card or His Attendance is Waived off
IF (V_ATTEN_CLOCK_IN IS NULL AND V_ATTEN_CLOCK_OUT IS NULL) THEN
--------- COMMENTED ON 17TH AUGUST 2017 ----------
/*
IF (V_EMP_RECORD.CARDNO IS NULL OR V_EMP_RECORD.ATD_WAIVEOFF = '1' OR
(V_EMP_RECORD.CARDNO IS NOT NULL AND
TO_DATE(V_ATTENDANCE_DATE, 'dd-MON-yy') <
TO_DATE(V_EMP_RECORD.CARD_ISSUE_DATE, 'dd-MON-yy'))) AND
(V_GH_FLAG = FALSE AND V_REST_FLAG = FALSE) THEN
V_ATTEN_CLOCK_IN := V_CALENDER_RECORD.CLOCKIN;
V_ATTEN_CLOCK_OUT := V_CALENDER_RECORD.CLOCKOUT;
DBMS_OUTPUT.PuT_LINE ('ATTENDANCE DATE: '||TO_DATE(V_ATTENDANCE_DATE, 'dd-MON-yy'));
DBMS_OUTPUT.PuT_LINE ('ISSUE DATE: '||TO_DATE(V_EMP_RECORD.CARD_ISSUE_DATE, 'dd-MON-yy'));
END IF;
*/ --------- END COMMENTED ON 17TH AUGUST 2017 ----------
-------- ADDED ON 17TH AUGUST 2016 --------------
IF (V_EMP_RECORD.CARDNO IS NULL OR V_EMP_RECORD.ATD_WAIVEOFF = '1') AND
------------------- ------************* COMMENTED ON 17th AUG 2016 ****************----------------
--- TO_DATE(V_ATTENDANCE_DATE, 'dd-MON-yy') < TO_DATE(V_EMP_RECORD.CARD_ISSUE_DATE, 'dd-MON-yy') THEN
V_ATTENDANCE_DATE < V_EMP_RECORD.CARD_ISSUE_DATE THEN
V_ATTEN_CLOCK_IN := V_CALENDER_RECORD.CLOCKIN;
V_ATTEN_CLOCK_OUT := V_CALENDER_RECORD.CLOCKOUT;
DBMS_OUTPUT.PuT_LINE ('M HEER ATTENDANCE DATE: '||TO_DATE(V_ATTENDANCE_DATE, 'dd-MON-yy'));
DBMS_OUTPUT.PuT_LINE ('ISSUE DATE: '||TO_DATE(V_EMP_RECORD.CARD_ISSUE_DATE, 'dd-MON-yy'));
END IF;
-- ELSIF (V_ATTEN_CLOCK_IN IS NULL AND V_ATTEN_CLOCK_OUT IS NULL)AND ( TO_DATE(V_ATTENDANCE_DATE, 'dd-MON-yy') < TO_DATE(V_EMP_RECORD.CARD_ISSUE_DATE, 'dd-MON-yy'))
-- AND V_EMP_RECORD.CARDNO IS NOT NULL THEN
------------************ UPPER LINES COMMENTED ON 17TH AUGUST 2016 *****************-------------------
ELSIF (V_ATTEN_CLOCK_IN IS NULL AND V_ATTEN_CLOCK_OUT IS NULL)AND ( V_ATTENDANCE_DATE < V_EMP_RECORD.CARD_ISSUE_DATE)
AND V_EMP_RECORD.CARDNO IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE ('ELSIF (V_ATTEN_CLOCK_IN IS NULL AND V_ATTEN_CLOCK_OUT IS NULL)AND ( V_ATTENDANCE_DATE < V_EMP_RECORD.CARD_ISSUE_DATE)
AND V_EMP_RECORD.CARDNO IS NOT NULL THEN
');
V_ATTEN_CLOCK_IN := V_CALENDER_RECORD.CLOCKIN;
V_ATTEN_CLOCK_OUT := V_CALENDER_RECORD.CLOCKOUT;
END IF;
-------- END ADDED ON 17TH AUGUST 2016 --------------
-- If Employee Is not Issued RFID Card or His Attendance is Waived off AND OT/CPL is Approved
IF (V_ATTEN_CLOCK_IN IS NULL AND V_ATTEN_CLOCK_OUT IS NULL) THEN
IF (V_EMP_RECORD.CARDNO IS NULL OR V_EMP_RECORD.ATD_WAIVEOFF = '1') OR
(V_EMP_RECORD.CARDNO IS NOT NULL AND
------------------- ------************* COMMENTED ON 17th AUG 2016 ****************----------------
---TO_DATE(V_ATTENDANCE_DATE, 'dd-MON-yyyy') < TO_DATE(V_EMP_RECORD.CARD_ISSUE_DATE, 'dd-MON-yyyy'))
-------AND ((V_GH_FLAG = TRUE OR V_REST_FLAG = TRUE)) ------************* COMMENTED ON 17th AUG 2016 ****************----------------
V_ATTENDANCE_DATE < V_EMP_RECORD.CARD_ISSUE_DATE)
THEN --- AND V_OT_APPROVED_WF = 1) THEN
DBMS_OUTPUT.PUT_LINE ('V_ATTENDANCE_DATE'||V_ATTENDANCE_DATE);
DBMS_OUTPUT.PUT_LINE ('V_EMP_RECORD.CARD_ISSUE_DATE'||V_EMP_RECORD.CARD_ISSUE_DATE);
V_ATTEN_CLOCK_IN := V_CALENDER_RECORD.CLOCKIN;
V_ATTEN_CLOCK_OUT := V_CALENDER_RECORD.CLOCKOUT;
DBMS_OUTPUT.PuT_LINE ('I AM HERE');
END IF;
END IF;
-- IF Clock IN is NULL Then Clock In IS Max Allowed Clock In Time
IF V_ATTEN_CLOCK_IN IS NULL AND V_ATTEN_CLOCK_OUT IS NOT NULL THEN
V_ATTEN_CLOCK_IN := V_CALENDER_RECORD.MAXCLOCKINTIME;
V_CLOCKIN_CHANGE_FLAG := '1';
END IF;
-- IF Clock OUT is NULL Then Clock OUT IS in Allowed Clock Out Time
IF V_ATTEN_CLOCK_OUT IS NULL AND V_ATTEN_CLOCK_IN IS NOT NULL THEN
V_ATTEN_CLOCK_OUT := V_CALENDER_RECORD.MINCLOCKOUTTIME;
V_CLOCKOUT_CHANGE_FLAG := '1';
END IF;
EXCEPTION
-- If Employee has not Swapped Card on That Date
WHEN NO_DATA_FOUND THEN
V_ATTEN_CLOCK_IN := NULL;
V_ATTEN_CLOCK_OUT := NULL;
END;
--*****************************************************************
--Get IN/Out Time In Format
/*
Purpose:
Gets the Clock In and Clock Out Time of Selected date and Format it.
When We Get Date Time from RFID Schema it is not in proper format. This
Function formats it in proper Date/Time Format.
Parameters:
P_1: V_ATTENDANCE_DATE Processing Date
p_2: V_EMP_RECORD Employee Record
p_3: V_CALENDER_RECORD Calender Record For Selected Date and Employee
*/
FUNCTION GET_CLOCK_TIME(P_CLOCK_DATE VARCHAR2, P_TIME VARCHAR2) RETURN DATE IS
BEGIN
RETURN TO_DATE(SUBSTR(P_CLOCK_DATE, 7, 2) ||
SUBSTR(P_CLOCK_DATE, 5, 2) ||
SUBSTR(P_CLOCK_DATE, 1, 4) || ' ' ||
SUBSTR(P_TIME, 1, 2) || ':' || SUBSTR(P_TIME, 3, 2),
'ddmmyyyy hh24:mi');
END;
--*****************************************************************
--Get IN/Out Time In Format
/*
Purpose:
This Function gets The Minimum And Maximum Clock IN/OUT Time
For Specific Date.
Parameters:
P_1: V_SHIFT Shift
p_2: V_ATTENDANCE_DATE In/Out Date
p_3: V_MIN_MAX_DATE Flag that checks If Time is IN or OUT. If Value is
'0' than IN Else OUT
*/
FUNCTION GET_MIN_MAX_DATE(V_CALENDER_RECORD TYPE_CALENDER_RECORD,
V_ATTENDANCE_DATE DATE,
V_EMP_RECORD TPYE_EMP_RECORD,
V_MIN_MAX_DATE CHAR) RETURN DATE IS
V_MINDATE TAD_CALENDARDETAIL.MINCLOCKINTIME%TYPE;
V_MAXDATE TAD_CALENDARDETAIL.MAXCLOCKOUTTIME%TYPE;
V_MINDATE_NEXT TAD_CALENDARDETAIL.MINCLOCKINTIME%TYPE;
V_MAXDATE_NEXT TAD_CALENDARDETAIL.MAXCLOCKOUTTIME%TYPE;
V_CALENDER_NEXT_RECORD TYPE_CALENDER_RECORD;
BEGIN
V_CALENDER_NEXT_RECORD := GET_CALENDER(V_EMP_RECORD,
V_ATTENDANCE_DATE + 1);
SELECT D.MINCLOCKINTIME, D.MAXCLOCKOUTTIME
INTO V_MINDATE, V_MAXDATE
FROM TAD_CALENDARDETAIL D
WHERE D.CALENDARID = V_CALENDER_RECORD.CALENDERID;
/*
AND UPPER(TRIM(D.DAY)) =
UPPER(TRIM(TO_CHAR(V_ATTENDANCE_DATE, 'Day')));
*/
SELECT D.MINCLOCKINTIME, D.MAXCLOCKOUTTIME
INTO V_MINDATE_NEXT, V_MAXDATE_NEXT
FROM TAD_CALENDARDETAIL D
WHERE D.CALENDARID = V_CALENDER_NEXT_RECORD.CALENDERID;
/*
AND UPPER(TRIM(D.DAY)) =
UPPER(TRIM(TO_CHAR(V_ATTENDANCE_DATE + 1, 'Day')));
*/
-- Minimum Clock Time
IF V_MIN_MAX_DATE = 0 THEN
RETURN TO_DATE(TO_CHAR(V_ATTENDANCE_DATE, 'dd-Mon-yyyy') ||
TO_CHAR(V_MINDATE, 'hh:mi am'),
'dd-Mon-yyyy hh:mi am');
-- Maximum Clock Time
ELSE
IF V_CALENDER_NEXT_RECORD.CALENDERID <> V_CALENDER_RECORD.CALENDERID AND
V_MAXDATE > V_MINDATE_NEXT THEN
V_MAXDATE := V_MINDATE_NEXT;
END IF;
IF V_MINDATE > V_MAXDATE THEN
RETURN TO_DATE(TO_CHAR(V_ATTENDANCE_DATE, 'dd-Mon-yyyy') ||
TO_CHAR(V_MAXDATE, 'hh:mi am'),
'dd-Mon-yyyy hh:mi am') + 1;
ELSE
RETURN TO_DATE(TO_CHAR(V_ATTENDANCE_DATE, 'dd-Mon-yyyy') ||
TO_CHAR(V_MAXDATE, 'hh:mi am'),
'dd-Mon-yyyy hh:mi am');
END IF;
END IF;
END;
--*****************************************************************
--Get IN/Out Time In Format
/*
Purpose:
This Function gets The Standard Clock IN/OUT Time
For Specific Date.
*/
FUNCTION GET_STD_CLOCK_IN_OUT(V_CLOCK_IN DATE,
V_CLOCK_OUT DATE,
V_ATTENDANCE_DATE DATE,
V_CLOCK_IN_OUT_FLAG CHAR) RETURN DATE IS
BEGIN
IF V_CLOCK_IN_OUT_FLAG = 0 THEN
RETURN TO_DATE(TO_CHAR(V_CLOCK_IN + TO_NUMBER(TRUNC(TO_DATE(V_ATTENDANCE_DATE)) - TRUNC(V_CLOCK_IN)), 'dd-MON-yy hh:mi am'), 'dd-MON-yy hh:mi am');
ELSIF V_CLOCK_IN_OUT_FLAG = 1 THEN
IF TO_DATE(TO_CHAR(V_CLOCK_IN +
TO_NUMBER(TRUNC(TO_DATE(V_ATTENDANCE_DATE)) -
TRUNC(V_CLOCK_IN)),
'dd-MON-yy hh:mi am'),
'dd-MON-yy hh:mi am') >
TO_DATE(TO_CHAR(V_CLOCK_OUT +
TO_NUMBER(TRUNC(TO_DATE(V_ATTENDANCE_DATE)) -
TRUNC(V_CLOCK_OUT)),
'dd-MON-yy hh:mi am'),
'dd-MON-yy hh:mi am') THEN
RETURN TO_DATE(TO_CHAR(V_CLOCK_OUT +
TO_NUMBER(TRUNC(TO_DATE(V_ATTENDANCE_DATE)) -
TRUNC(V_CLOCK_OUT)),
'dd-MON-yy hh:mi am'),
'dd-MON-yy hh:mi am') + 1;
ELSE
RETURN TO_DATE(TO_CHAR(V_CLOCK_OUT +
TO_NUMBER(TRUNC(TO_DATE(V_ATTENDANCE_DATE)) -
TRUNC(V_CLOCK_OUT)),
'dd-MON-yy hh:mi am'),
'dd-MON-yy hh:mi am');
END IF;
END IF;
END;
FUNCTION IS_LOCATION_ACTIVE(V_LOCATIONID VARCHAR2)
RETURN NUMBER IS
V_FLAG NUMBER(1);
BEGIN
/*
SELECT DISTINCT R.STATUS
INTO V_FLAG
FROM TAD_RFIDLOCATIONS R
WHERE R.LOCATIONID = V_LOCATIONID;
*/
/*
select
qr.CHARACTER1
INTO V_FLAG
from qa_results qr
where 1=1
and qr.PLAN_ID = 3375
and qr.character1 = V_LOCATIONID;
*/
select hl.ATTRIBUTE1 into v_flag
from hr_locations hl
where hl.location_id = V_LOCATIONID;
RETURN V_FLAG;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN 0;
END;
--*****************************************************************
--Procedure which Process attendance
/*
Purpose:
Following are the steps of processing Attendance
1. Download Attendance from RFID Schema of Employee
2. Get Total Days of Processing Attendance
3. Process Attendance of Each Day
4. Check If Day is GH Holiday
5. Check If Attendance is Rest Day
6. Get Calender
7. Get Clock In and Out of that Date
8. Get Attendance Type
9. Check If Record Alredy Exists In Attendance Register against
processing Date.
10. Insert/Update Attendance Register
11. Flag Attendance Date In Attendance Log Table. Attendance is processed
Parameters:
P_1: V_EMPID THE Employee Whose Attendance we are going TO Process
P_2: V_USERID User who is processing the attendance
p_3: V_DOWNLOAD_UPTO Attendance will be processed till this date.
*/
PROCEDURE PROCESS_ATTENDANCE(V_EMPID EMPMASTERINFO.EMPLOYEEID%TYPE,
V_USERID varchar2, ---HXC_TK_GROUPS.TK_GROUP_ID%TYPE,
V_DATE DATE) IS
V_EMP_RECORD TPYE_EMP_RECORD;
V_CALENDER_RECORD TYPE_CALENDER_RECORD;
V_ATTENDANCE_FROM DATE;
V_ATTENDANCE_TO DATE;
V_ATTENDANCE_DATE DATE;
V_TOTAL_DAYS_COUNT NUMBER(5);
V_GH_FLAG BOOLEAN := FALSE;
V_REST_FLAG BOOLEAN := FALSE;
V_ATTEND_TYPE VARCHAR2(5);
V_WORKED_HOURS NUMBER(5);
V_EXTRA_HOURS NUMBER(5);
V_MIN_EXTRA_HOURS NUMBER(5);
V_OT_APPROVED_FLAG NUMBER(2);
V_RESULT VARCHAR2(100);
----- V_PROCESSID HCMPROCESSDETAIL.PROCESSID%TYPE := CMN_UPDATEPROCESSINFO_SEQ.NEXTVAL;
BEGIN
/*
CMN_UPDATEPROCESSINFO_START('TAD_RFID_DOWNLOAD.PROCESS_ATTENDANCE',
V_USERID,
V_EMPID || ',' || V_USERID || ',' || V_DATE,
'',
V_PROCESSID);
*/
--Get Employee Record
SELECT distinct E.EMPLOYEEID,
E.SHIFT,
E.DESIGNATIONID,
E.RESTDAY01,
E.RESTDAY02,
E.LOCATIONID,
E.MAINID,
E.EMPCATEGORY,
E.PAYGROUPID,
E.GRADEID,
NVL(E.OT_ALLOWED, 0),
NVL(E.CPL_ALLOWED, 0),
NVL(E.GHL_ALLOWED, 0),
-- CASE
-- WHEN
-- IS_LOCATION_ACTIVE(E.LOCATIONID) = '0' THEN
-- '0'
-- ELSE
-- '1'
-- END,
'0',
e.CARDNO, ----RDNO. DECODE(T.STATUS, '1', T.CARDNO, NULL),
--NULL,
--e.ISSUEDATE,
e.CARD_ISSUE_DATE,
E.EMPTYPE,
E.PERIODICAL_AMOUNT
INTO V_EMP_RECORD
FROM EMPMASTERINFO E ----, EPR_EMP_PARAMETERS P, TAD_RFIDEMPCARDS T
WHERE 1=1
AND E.EMPLOYEEID = V_EMPID;
/*
AND P.EMPLOYEEID(+) = E.EMPLOYEEID
AND T.EMPLOYEEID(+) = E.EMPLOYEEID;
*/
/* V_ATTENDANCE_FROM := LEAST(GET_LAST_PROCESS_DATE(V_EMP_RECORD.EMPLOYEEID,
V_EMP_RECORD.PAYGROUPID,
V_EMP_RECORD.ATD_WAIVEOFF),
TO_DATE(SYSDATE)); */ ---------------------- change by tanveer 03-sep-2016
V_ATTENDANCE_FROM := NVL(GET_LAST_PROCESS_DATE(V_EMP_RECORD.EMPLOYEEID,
V_EMP_RECORD.PAYGROUPID,
V_EMP_RECORD.ATD_WAIVEOFF),TRUNC(V_DATE,'MON'));
-- V_ATTENDANCE_TO := LEAST(V_DATE, TO_DATE(SYSDATE)); --- change by tanveer 03-sep-2016
V_ATTENDANCE_TO := V_DATE;
V_ATTENDANCE_TO := V_DATE;
-- GET_LAST_CHECKIN_DATE(V_EMP_RECORD.EMPLOYEEID);
----IF to_date(V_ATTENDANCE_FROM,'dd-MON-yyyy') > to_date(V_ATTENDANCE_TO,'dd-MON-yyyy') THEN
--------------- ********************** COMMENTED UPPER LINE ON 17TH AUGUST *********************----------------------
IF V_ATTENDANCE_FROM > V_ATTENDANCE_TO THEN
V_ATTENDANCE_TO := V_ATTENDANCE_FROM;
DBMS_OUTPUT.PUT_LINE('V_ATTENDANCE_FROM > V_ATTENDANCE_TO'||V_ATTENDANCE_TO);
END IF;
--2. Get Total Days of Processing Attendance
V_TOTAL_DAYS_COUNT := ABS(ROUND(V_ATTENDANCE_TO - V_ATTENDANCE_FROM)) + 1;
----ABS(ROUND(TO_DATE(V_ATTENDANCE_TO, 'dd-MON-yy') - TO_DATE(V_ATTENDANCE_FROM, 'dd-MON-yy'))) + 1;
--------------- ********************** COMMENTED UPPER LINE ON 17TH AUGUST *********************----------------------
--3. Process Attendance of Each Day
FOR I IN 0 .. V_TOTAL_DAYS_COUNT - 1 LOOP
dbms_output.put_line ('FOR I IN 0 .. V_TOTAL_DAYS_COUNT - 1 LOOP');
--Initialize Variables
V_ATTEN_CLOCK_IN := NULL;
V_ATTEN_CLOCK_OUT := NULL;
V_REST_FLAG := FALSE;
V_ATTENDANCE_DATE := V_ATTENDANCE_FROM + I;
dbms_output.put_line ('V_ATTENDANCE_DATE := V_ATTENDANCE_FROM + I;'||TO_CHAR(V_ATTENDANCE_DATE,'DD-MON-YYYY') ||'::'||V_DATE);
--4. Check If Day is GH Holiday
V_GH_FLAG := IS_GH(TO_DATE(V_ATTENDANCE_DATE));
--5. Check If Attendance is Rest Day
IF UPPER(TRIM(TO_CHAR(V_ATTENDANCE_DATE, 'Day'))) =
UPPER(TRIM(V_EMP_RECORD.RESTDAY01)) THEN
V_REST_FLAG := TRUE;
END IF;
--6. Get Calender
V_CALENDER_RECORD := GET_CALENDER(V_EMP_RECORD, V_ATTENDANCE_DATE);
--7. Get Clock In and Out of that Date
GET_CLOCK_INOUT(V_ATTENDANCE_DATE,
V_EMP_RECORD,
V_CALENDER_RECORD,
V_GH_FLAG,
V_REST_FLAG);
--8. Get Attendance Type
V_ATTEND_TYPE := GET_ATTENDANCE_TYPE((CASE
WHEN V_ATTEN_CLOCK_IN IS NULL AND
V_ATTEN_CLOCK_OUT IS NULL THEN
FALSE
ELSE
TRUE
END),
V_REST_FLAG,
V_GH_FLAG,
V_EMP_RECORD);
/*
IF V_ATTEND_TYPE IN ('P', 'OT', 'C') AND
(V_ATTEN_CLOCK_IN IS NULL OR V_ATTEN_CLOCK_OUT IS NULL) THEN
RAISE_APPLICATION_ERROR(-20001,
'Could Not Down Load Attendance of Date: ' ||
V_ATTENDANCE_DATE || ' of Employee: ' ||
V_EMP_RECORD.EMPLOYEEID ||
' Card In/Out is NULL');
END IF;
SELECT DECODE(MAX(R.STATUS), 'ARROVED', 1, 'PROCESSED', 1, 0)
INTO V_OT_APPROVED_FLAG
FROM TAD_OT_CPL_REGISTER R
WHERE R.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND R.OT_CPL_DATE = V_ATTENDANCE_DATE;
*/
V_MIN_EXTRA_HOURS := 0; ---TO_NUMBER(GET_CMN_PARAMETER('MINCPLHOURS'));
V_WORKED_HOURS := NVL(ROUND((V_ATTEN_CLOCK_OUT - V_ATTEN_CLOCK_IN) * 24),
0);
V_EXTRA_HOURS := 0;
/*
CASE
WHEN V_ATTEND_TYPE IN ('C', 'OT') THEN
V_WORKED_HOURS
WHEN V_ATTEND_TYPE IN ('R', 'GH', 'A') THEN
0
WHEN V_WORKED_HOURS - 0
-- TO_NUMBER(GET_CMN_PARAMETER('STANDARDHOURS'))
>=
V_MIN_EXTRA_HOURS THEN
V_WORKED_HOURS -
TO_NUMBER(GET_CMN_PARAMETER('STANDARDHOURS'))
ELSE
0
END;
*/
--9. Insert/Update Record Into Attendance Register
MERGE INTO TAD_REGISTER TT
USING DUAL
ON (TT.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID AND TT.ATTENDANCEDATE = V_ATTENDANCE_DATE)
WHEN MATCHED THEN
UPDATE
SET TT.CLOCKIN = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.CLOCKIN
ELSE
V_ATTEN_CLOCK_IN
END,
TT.CLOCKOUT = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.CLOCKOUT
ELSE
V_ATTEN_CLOCK_OUT
END,
TT.ATTENDANCETYPE =
CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.ATTENDANCETYPE
ELSE
V_ATTEND_TYPE
END,
TT.WORKEDHOURS = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.WORKEDHOURS
ELSE
V_WORKED_HOURS
END,
TT.ACTUALHOURS = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.ACTUALHOURS
ELSE
V_EXTRA_HOURS
END,
TT.DEFAULTCLOCKIN = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.DEFAULTCLOCKIN
ELSE
V_ATTEN_CLOCK_IN
END,
TT.DEFAULTCLOCKOUT = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.DEFAULTCLOCKOUT
ELSE
V_ATTEN_CLOCK_OUT
END,
TT.DEFAULTATTENDANCETYPE =
CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.DEFAULTATTENDANCETYPE
ELSE
V_ATTEND_TYPE
END,
TT.DEFAULTCLOCKINFLAG = V_CLOCKIN_CHANGE_FLAG,
TT.DEFAULTCLOCKOUTFLAG = V_CLOCKOUT_CHANGE_FLAG,
TT.SHIFT = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.SHIFT
ELSE
V_CALENDER_RECORD.SHIFT
END,
TT.CALENDARID = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.CALENDARID
ELSE
V_CALENDER_RECORD.CALENDERID
END,
TT.CARDNO = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.CARDNO
ELSE
V_EMP_RECORD.CARDNO
END,
TT.DOWNLOADEDON = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.DOWNLOADEDON
ELSE
SYSDATE
END,
TT.DOWNLOADEDBY = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.DOWNLOADEDBY
ELSE
V_USERID
END,
TT.GH_FLAG = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.GH_FLAG
ELSE
DECODE(V_ATTEND_TYPE, 'GH', '1', '0')
END,
TT.REST_FLAG = CASE
WHEN TT.AUTOFLAG = '0' OR TT.STATUS = 1 OR V_OT_APPROVED_FLAG = '1' THEN
TT.REST_FLAG
ELSE
DECODE(V_ATTEND_TYPE, 'R', '1', '0')
END WHEN NOT MATCHED THEN INSERT(EMPLOYEEID, ATTENDANCEDATE, PATH, LOCATIONID, CATEGORY, FUNC_DESIGNATION, SHIFT, PAYGROUPID, GRADEID, ATTENDANCETYPE, CLOCKIN, CLOCKOUT, CALENDARID, DOWNLOADEDON, DOWNLOADEDBY, AUTOFLAG, WORKEDHOURS, ACTUALHOURS, CLOCKINCHANGE, CLOCKOUTCHANGE, DEFAULTCLOCKIN, DEFAULTCLOCKOUT, DEFAULTATTENDANCETYPE, CARDNO, DEFAULTCLOCKINFLAG, DEFAULTCLOCKOUTFLAG, GH_FLAG, REST_FLAG, EMPTYPE, STATUS) VALUES(V_EMP_RECORD.EMPLOYEEID, V_ATTENDANCE_DATE, V_EMP_RECORD.MAINID, V_EMP_RECORD.LOCATIONID, V_EMP_RECORD.EMPCAEGORY, V_EMP_RECORD.DESIGNATIONID, NVL2(V_CALENDER_RECORD.SHIFT, V_CALENDER_RECORD.SHIFT, V_EMP_RECORD.SHIFT), V_EMP_RECORD.PAYGROUPID, V_EMP_RECORD.GRADEID, V_ATTEND_TYPE, V_ATTEN_CLOCK_IN, V_ATTEN_CLOCK_OUT, V_CALENDER_RECORD.CALENDERID, SYSDATE, V_USERID, '1', V_WORKED_HOURS, V_EXTRA_HOURS, 0, 0, V_ATTEN_CLOCK_IN, V_ATTEN_CLOCK_OUT, V_ATTEND_TYPE, V_EMP_RECORD.CARDNO, V_CLOCKIN_CHANGE_FLAG, V_CLOCKOUT_CHANGE_FLAG, DECODE(V_ATTEND_TYPE, 'GH', '1', 'OT', '1', '0'), DECODE(V_ATTEND_TYPE, 'R', '1', 'C', '1', '0'), V_EMP_RECORD.EMPTYPE, '0');
--9. Insert/Update Record Into OT/CPL Register
/*
IF V_EMP_RECORD.OT_ALLOWED = '1' AND V_EXTRA_HOURS > 0 THEN
IF V_ATTEND_TYPE IN ('P', 'OT', 'C') THEN
MERGE INTO TAD_OT_CPL_REGISTER C
USING DUAL
ON (C.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID AND C.OT_CPL_DATE = V_ATTENDANCE_DATE)
WHEN MATCHED THEN
UPDATE
SET C.WORKED_HOURS = CASE
WHEN C.STATUS = 'PENDING' THEN
V_WORKED_HOURS
ELSE
C.WORKED_HOURS
END,
C.CPLOROTFLAG = CASE
WHEN C.STATUS = 'PENDING' THEN
DECODE(V_ATTEND_TYPE, 'C', '0', 'OT', '2', '1')
ELSE
C.CPLOROTFLAG
END,
C.HOUR = CASE
WHEN C.STATUS = 'PENDING' THEN
V_EXTRA_HOURS
ELSE
C.HOUR
END,
C.ENTEREDBY = CASE
WHEN C.STATUS = 'PENDING' THEN
V_USERID
ELSE
C.ENTEREDBY
END,
C.ENTRYDATE = CASE
WHEN C.STATUS = 'PENDING' THEN
SYSDATE
ELSE
C.ENTRYDATE
END,
C.APPROVED_HOURS = CASE
WHEN C.STATUS = 'PENDING' THEN
NVL(NVL((SELECT W.CPL_HOURS
FROM TAD_OT_CPL_WF W
WHERE W.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND W.CPL_DATE = V_ATTENDANCE_DATE
AND W.APPROVAL_STATUS = 'APPROVED'),
C.APPROVED_HOURS),
0)
ELSE
C.APPROVED_HOURS
END,
C.STATUS = CASE
WHEN NVL((SELECT W.CPL_HOURS
FROM TAD_OT_CPL_WF W
WHERE W.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND W.APPROVAL_STATUS = 'APPROVED'
AND W.CPL_DATE = TO_DATE(V_ATTENDANCE_DATE, 'dd-mm-yyyy')),
0) > 0 AND C.STATUS = 'PENDING' THEN
'APPROVED'
ELSE
C.STATUS
END,
C.CPL_NUMBER =
(SELECT W.CPL_NUMBER
FROM TAD_OT_CPL_WF W
WHERE W.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND W.CPL_DATE = V_ATTENDANCE_DATE
AND W.APPROVAL_STATUS = 'APPROVED'),
C.ENTRYMODE = 'TAD_RFID_DOWNLOAD.PROCESS_ATTENDANCE'
WHEN NOT MATCHED THEN
INSERT
(EMPLOYEEID,
OT_CPL_DATE,
WORKED_HOURS,
HOUR,
CPLOROTFLAG,
STATUS,
SHIFT,
FLAG,
ENTRYDATE,
ENTEREDBY,
PATH,
LOCATIONID,
PAYGROUPID,
CATEGORY,
FUNC_DESIGNATION,
EMPTYPE,
GRADEID,
APPROVED_HOURS,
CPL_NUMBER,
ENTRYMODE,
GROSS_SALARY)
VALUES
(V_EMP_RECORD.EMPLOYEEID,
V_ATTENDANCE_DATE,
V_WORKED_HOURS,
V_EXTRA_HOURS,
DECODE(V_ATTEND_TYPE, 'C', '0', 'OT', '2', '1'),
--'PENDING',
CASE
WHEN NVL((SELECT W.CPL_HOURS
FROM TAD_OT_CPL_WF W
WHERE W.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND W.APPROVAL_STATUS = 'APPROVED'
AND W.CPL_DATE = TO_DATE(V_ATTENDANCE_DATE, 'dd-mm-yyyy')),
0) > 0 THEN
'APPROVED'
ELSE
'PENDING'
END,
V_EMP_RECORD.SHIFT,
'0',
SYSDATE,
V_USERID,
V_EMP_RECORD.MAINID,
V_EMP_RECORD.LOCATIONID,
V_EMP_RECORD.PAYGROUPID,
V_EMP_RECORD.EMPCAEGORY,
V_EMP_RECORD.DESIGNATIONID,
V_EMP_RECORD.EMPTYPE,
V_EMP_RECORD.GRADEID,
NVL((SELECT W.CPL_HOURS
FROM TAD_OT_CPL_WF W
WHERE W.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND W.CPL_DATE = V_ATTENDANCE_DATE
AND W.APPROVAL_STATUS = 'APPROVED'),
0),
(SELECT W.CPL_NUMBER
FROM TAD_OT_CPL_WF W
WHERE W.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND W.CPL_DATE = V_ATTENDANCE_DATE
AND W.APPROVAL_STATUS = 'APPROVED'),
'TAD_RFID_DOWNLOAD.PROCESS_ATTENDANCE',
V_EMP_RECORD.GROSS_SALARY);
--ELSIF V_ATTEND_TYPE IN ('R', 'GH', 'A') THEN
ELSE
INSERT INTO TAD_OT_CPL_REGISTER_DEL_BK
SELECT *
FROM TAD_OT_CPL_REGISTER O
WHERE O.OT_CPL_DATE = V_ATTENDANCE_DATE
AND O.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND (O.EMPLOYEEID, O.OT_CPL_DATE) =
(SELECT R.EMPLOYEEID, R.ATTENDANCEDATE
FROM TAD_REGISTER R
WHERE R.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND R.ATTENDANCEDATE = V_ATTENDANCE_DATE
AND R.AUTOFLAG = '1')
AND O.STATUS <> 'PROCESSED';
DELETE FROM TAD_OT_CPL_REGISTER O
WHERE O.OT_CPL_DATE = V_ATTENDANCE_DATE
AND O.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND (O.EMPLOYEEID, O.OT_CPL_DATE) =
(SELECT R.EMPLOYEEID, R.ATTENDANCEDATE
FROM TAD_REGISTER R
WHERE R.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND R.ATTENDANCEDATE = V_ATTENDANCE_DATE
AND R.AUTOFLAG = '1')
AND O.STATUS <> 'PROCESSED';
END IF;
IF (TAD_OTCPL_WF.IS_OTCPL_EXIST(V_EMP_RECORD.EMPLOYEEID,
V_ATTENDANCE_DATE) = 1 AND
TAD_OTCPL_WF.IS_OTCPL_APPROVED(V_EMP_RECORD.EMPLOYEEID,
V_ATTENDANCE_DATE) = 1) THEN
TAD_OTCPL_WF.CREDIT_OTCPL_WF(V_EMP_RECORD.EMPLOYEEID,
V_ATTENDANCE_DATE);
TAD_PROCESSOTCPL.CREDITINDIVIDUALOTCPL(V_EMP_RECORD.EMPLOYEEID,
V_ATTENDANCE_DATE,
V_USERID,
'TAD_RFID_DOWNLOAD.DOWNLOAD_RFID_ATTENDANCE',
V_RESULT);
END IF;
ELSE
INSERT INTO TAD_OT_CPL_REGISTER_DEL_BK
SELECT *
FROM TAD_OT_CPL_REGISTER O
WHERE O.OT_CPL_DATE = V_ATTENDANCE_DATE
AND O.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND (O.EMPLOYEEID, O.OT_CPL_DATE) =
(SELECT R.EMPLOYEEID, R.ATTENDANCEDATE
FROM TAD_REGISTER R
WHERE R.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND R.ATTENDANCEDATE = V_ATTENDANCE_DATE
AND R.AUTOFLAG = '1')
AND O.STATUS <> 'PROCESSED';
DELETE FROM TAD_OT_CPL_REGISTER O
WHERE O.OT_CPL_DATE = V_ATTENDANCE_DATE
AND O.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND (O.EMPLOYEEID, O.OT_CPL_DATE) =
(SELECT R.EMPLOYEEID, R.ATTENDANCEDATE
FROM TAD_REGISTER R
WHERE R.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND R.ATTENDANCEDATE = V_ATTENDANCE_DATE
AND R.AUTOFLAG = '1')
AND O.STATUS <> 'PROCESSED';
END IF;
*/
END LOOP;
--11. Flag Attendance Date In Attendance Log Table. Attendance
-- is processed
UPDATE TAD_EMPATTENDANCELOG E
SET E.STATUS = '1', E.UPDATEDON = SYSDATE, E.UPDATEDBY = V_USERID
WHERE E.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID
AND E.STATUS = '0'
----------------- COMMENTED ON 18 AUG 2016 --------------------
---- AND TO_DATE(E.SWAPDATETIME, 'dd-Mon-yyyy') < TO_DATE(V_ATTENDANCE_TO, 'dd-Mon-yyyy');
AND TRUNC(E.SWAPDATETIME) < V_ATTENDANCE_TO;
/*
UPDATE TAD_INDIVIDUALROTATION R
SET R.STATUS = 1
WHERE R.FROMDATE <= V_ATTENDANCE_TO
AND R.EMPLOYEEID = V_EMP_RECORD.EMPLOYEEID;
*/
------------ CMN_UPDATEPROCESSINFO_END(V_PROCESSID);
COMMIT;
/* -------------------- COMMENTED ON 16 AUG 2016
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
SEC_ERR_LOG(SQLCODE,
SQLERRM,
'TAD_RFID_DOWNLOAD.PROCESS_ATTENDANCE',
V_USERID,
'Could Not Down Load Attendance of Date: ' ||
V_ATTENDANCE_DATE || ' of Employee: ' ||
V_EMP_RECORD.EMPLOYEEID || ' Error Message: ' || SQLCODE || ' ' ||
SQLERRM);
*/
-- CMN_UPDATEPROCESSINFO_ERROR(V_PROCESSID, SQLCODE, SQLERRM);
/*
RAISE_APPLICATION_ERROR(-20001,
'Could Not Down Load Attendance of Date: ' ||
V_ATTENDANCE_DATE || ' of Employee: ' ||
V_EMP_RECORD.EMPLOYEEID || ' Error Message: ' ||
SQLCODE || ' ' || SQLERRM);
*/
END;
END TAD_RFID_DOWNLOAD;
/
No comments:
Post a Comment