Untitled diff

Created Diff never expires
CREATE OR REPLACE PACKAGE APPS."HXT_TIME_ATTENDANCE_AEC" as
/*<TOAD_FILE_CHUNK>*/
-- The main package of the generate attendance
CREATE OR REPLACE PACKAGE BODY APPS."HXT_TIME_ATTENDANCE_AEC" as
Function GET_DATE_TYPE(P_PERSON_ID IN NUMBER, P_Date IN Date) Return Varchar2;
Function FIND_IN_DATE_YESTERDAY(P_DATE In Date, P_EMP_NO In Varchar2) Return Date;
Function Last_In_Yesterday(P_Date Date, P_Emp_No In Varchar2) Return Date;
Function Last_Out_Yesterday(P_Date Date, P_Emp_No In Varchar2) Return Date;
Function Last_Action_Yesterday(P_Date Date, P_Emp_No In Varchar2) Return Date;
Function Min_In_Today(P_Date Date, P_Emp_No In Varchar2) Return Date;
Function TIME_SUBTRACT(P_time1 IN Date, P_time2 IN Date) RETURN Number;
Procedure UPDATE_TEMPORARY_CARDS(P_Date In Date, P_Emp_No In Varchar2);
Function CONVERT_NUMBERTO_TIME(P_Counter In Number) Return Number;
Function CONVERT_TIMETO_NUMBER(P_C IN NUMBER) RETURN NUMBER;
Function RET_DATE(P_DATE IN VARCHAR2) RETURN VARCHAR2;
Procedure SET_YESTERDAY_RECORDS(P_Date In Date, P_EMP_NO In Varchar2,
P_Memory_In In Date, P_SRecords Out Boolean);
Procedure GET_EMPLOYEE_SHIFT_TIME (P_Person_ID IN Number, P_Date IN Date,
P_Date_Type IN Varchar2, P_BBreak_In Out Date, P_BBreak_Out Out Date,
P_ABreak_In Out Date, P_ABreak_Out Out Date, P_Attendance_Type Out Varchar2,
P_Shift_Name In Out Varchar2);
Procedure GET_LATE_TIME (P_Date IN Date, P_BBreak_In IN Date, P_BBreak_Out IN Date,
P_ABreak_In IN Date, P_ABreak_Out IN Date, P_Date_Time_In IN Date, P_Date_Time_Out IN Date,
P_Todays_Absent Out Number, P_Official_Work_Hours Out Date, P_Total_Work_Hours Out Date,
P_Tardiness Out Number, P_Tardiness_Late Out Number, P_Attendance_Type In Varchar2,
P_Late_Start Out Number, P_OverTime_Deduct Out Number);
Procedure GET_PLAN_DETAILS (P_Date IN Date, P_Person_ID IN Number, P_Date_Type IN Varchar2,
P_Official_Work_Hours In Date, P_Total_Work_Hours In Date,
P_OTR Out Number, P_OTS Out Number,
P_OTR_Approved Out Number, P_OTS_Approved Out Number,
P_Hazard_Days Out Number, P_Hazard_Days_Hight Out Number, P_Stand_By_Days Out Number,
P_Attendance_Type In Varchar2, P_LastRecord In Varchar2,
P_Plan_Flag In Out Varchar2, P_Plan_Flag1 In Out Varchar2, P_OverTime_Deduct In Number, P_Leave_Type In Varchar2); --P_leave_type added by hadba
Procedure TIME_ATTENDANCE_PERIOD (P_Date IN Date, P_Start_Date Out DATE, P_End_Date Out Date);
Function GET_LEAVE_IN_DAY (P_Date IN Date,P_PROJECTED_START IN Date,P_PROJECTED_END IN Date,P_ACTUAL_START IN Date,P_ACTUAL_END IN Date,P_PROJECTED_START_TIME IN VARCHAR2,
P_PROJECTED_END_TIME IN VARCHAR2,P_ACTUAL_START_TIME IN VARCHAR2,P_ACTUAL_END_TIME IN VARCHAR2, P_ABS_REASON_ID IN NUMBER) return NUMBER;
Function IS_WEEKEND_HOLIDAY(P_Date IN Date) return Varchar2;
Procedure GET_COUNTER_INFO (P_Person_ID IN Number,P_Date In Date, P_Tardiness IN NUMBER,
P_Todays_Absent IN Number, P_Period_Start IN Date, P_Period_End IN Date,
P_Counter_15_Minutes OUT Number, P_Counter_30_Minutes OUT Number,
P_Counter_60_Minutes OUT Number, P_Cumulative_Late_Hours OUT Number, P_Excuse_Status OUT Varchar2,
P_Total_Absent_Days OUT Number, P_Counter_Leave_Days Out Number, P_Leave_Type Out Varchar2,
P_Date_Time_In In Date, P_Official_Work_Hours In Date, P_Shift_Name In Varchar2,
P_Attendance_Type In Varchar2, P_LastRecrod In Varchar2,
P_Late_Start In Number, P_Counter_Flag In Out Varchar2);
Procedure Signe_Attendance (P_Date In Date);
PROCEDURE LEAVE_DIFFERENCE (P_Person_Id In Number, P_Period_Start In Date, P_Period_End In Date);
PROCEDURE load_overtime(P_Person_Id In Number, P_Period_Start In Date, P_Period_End In Date);
PROCEDURE SUBMIT_PLAN_CHECK(P_Person_Id In Number, P_Period_Start In Date, P_Period_End In Date);
PROCEDURE Gen_Attendance (ERRBUF VARCHAR2, RETCODE VARCHAR2, P_Date In Varchar2, P_EMP_NO In varchar2 default NULL);
PROCEDURE LOAD_ACTION (p_date IN DATE, P_EMP_NO In varchar2 default NULL);
Procedure BUILD_MGR_DEPT_HIERARCHY(P_MGR_ID IN Number,P_Sub_MGR_ID IN NUMBER, P_Level IN Number);
Procedure GET_DATE_TIME_IN_OUT(P_Date In Date, P_Emp_No In Varchar2,P_person_id In Number, P_Today_shift_in In DATE,P_update_yesterday_record Out Boolean);
Procedure PURGE_ATTENDANCE(P_Date In Date, P_Emp_No In Varchar2, ALL_EMP in Boolean);
END HXT_TIME_ATTENDANCE_AEC;
/
create or replace
PACKAGE BODY "HXT_TIME_ATTENDANCE_AEC" as
/****************************************************************************************/
/****************************************************************************************/
-- This is the main package which will run daily basis and calulate the time attandenc
-- This is the main package which will run daily basis and calulate the time attandenc
-- of the employee.
-- of the employee.
-- Gen_Attendance is the main procedure which will run the loop for each employee
-- Gen_Attendance is the main procedure which will run the loop for each employee
-- and will be calling the other procedures. Main loop will run once for each emplyee
-- and will be calling the other procedures. Main loop will run once for each emplyee
-- 1st system gets the start date and end date for the current attendance period.
-- 1st system gets the start date and end date for the current attendance period.
-- Then it finds the Type of current date i.e. its Ramandan date or normal date
-- Then it finds the Type of current date i.e. its Ramandan date or normal date
-- As on the basis of date type employees shift type is selected from employee plan
-- As on the basis of date type employees shift type is selected from employee plan
-- Now get the Employees Current Shift Timings i.e
-- Now get the Employees Current Shift Timings i.e
-- Before Break In , Before Break Out, After Break In, After Break Out in date format
-- Before Break In , Before Break Out, After Break In, After Break Out in date format
-- DD-MON-YYYY HH24:MI.
-- DD-MON-YYYY HH24:MI.
-- Then system checks that if the current employee data exist in temporary card issuance table
-- Then system checks that if the current employee data exist in temporary card issuance table
-- or not if it found then it will update the aciton table with the actual employee number
-- or not if it found then it will update the aciton table with the actual employee number
-- After that get the time in and time out into a table HXT_aciton_RECS_AEC
-- After that get the time in and time out into a table HXT_aciton_RECS_AEC
-- and update the Action Table Flag to Two for those Records whose time and time out fall in
-- and update the Action Table Flag to Two for those Records whose time and time out fall in
-- dual days.
-- dual days.
-- If the employee dont come at all in current date then blank date time in and out is eterd
-- If the employee dont come at all in current date then blank date time in and out is eterd
-- with other details. Other wise if he comes and and only his dual day record exist then
-- with other details. Other wise if he comes and and only his dual day record exist then
-- no record will be enterd. and next day his current records and prevuios day dual day
-- no record will be enterd. and next day his current records and prevuios day dual day
-- record will be enterd in HXT_ATTENDANCE_AEC.
-- record will be enterd in HXT_ATTENDANCE_AEC.
-- If employee remins more than a specific time period and come back in office then
-- If employee remins more than a specific time period and come back in office then
-- it will considerd his next shift so employee will be having more than one record in one day
-- it will considerd his next shift so employee will be having more than one record in one day
-- in HXT_ATTENDANCE_AEC.
-- in HXT_ATTENDANCE_AEC.
-- Then employees tardiness and absentism is calculated, And Employess Plans realted things are
-- Then employees tardiness and absentism is calculated, And Employess Plans realted things are
-- calculated such as overtime, standby days, harzad days low and high, Then Counters are maintained
-- calculated such as overtime, standby days, harzad days low and high, Then Counters are maintained
-- that how much time employee come late more than: 15 minutes, 30 minutes, and 60 minutes
-- that how much time employee come late more than: 15 minutes, 30 minutes, and 60 minutes
-- now all the required inofomation is gathered and calculated. it will be inserted into HXT_ATTENDANCE_AEC table
-- now all the required inofomation is gathered and calculated. it will be inserted into HXT_ATTENDANCE_AEC table
-- and some information is enterd in log file of the process
-- and some information is enterd in log file of the process
-- at the end of the loop
-- at the end of the loop
-- system update the record if the leave is enterd after the process run for the current day
-- system update the record if the leave is enterd after the process run for the current day
-- and then will also update the record if the plan is enterd after the process run for the current day
-- and then will also update the record if the plan is enterd after the process run for the current day
-- Then in the last after all loops ends system will
-- Then in the last after all loops ends system will
-- auto sign the attendance for 5 days back records which are not signed
-- auto sign the attendance for 5 days back records which are not signed
/*********************************************************************************/
/*********************************************************************************/
/* CLEANINIG: CODE CLEANED OUT on 13-May-2007 By Suhaibani */
/* CLEANINIG: CODE CLEANED OUT on 13-May-2007 By Suhaibani */
/* commented old useless code were removed */
/* commented old useless code were removed */
PROCEDURE Gen_Attendance (ERRBUF VARCHAR2, RETCODE VARCHAR2, P_Date In Varchar2, P_EMP_NO In varchar2 default NULL) IS
PROCEDURE Gen_Attendance (ERRBUF VARCHAR2, RETCODE VARCHAR2, P_Date In Varchar2, P_EMP_NO In varchar2 default NULL) IS
/*Gen_Attendance is the main procedure which will run the loop for each employee
/*Gen_Attendance is the main procedure which will run the loop for each employee
and will be calling the other procedures*/
and will be calling the other procedures*/
V_PDate DATE := to_date(substr(P_date,1,10),'YYYY/MM/DD');
V_PDate DATE := to_date(substr(P_date,1,10),'YYYY/MM/DD');
V_Date_Time_In DATE;
V_Date_Time_In DATE;
V_Date_Time_Out DATE;
V_Date_Time_Out DATE;
V_Total_Work_Hours DATE;
V_Total_Work_Hours DATE;
V_Official_Work_Hours DATE;
V_Official_Work_Hours DATE;
V_Tardiness Number := 0;
V_Tardiness Number := 0;
V_Tardiness_Late Number := 0;
V_Tardiness_Late Number := 0;
V_Todays_Absent Number := 0;
V_Todays_Absent Number := 0;
V_Weekly_Absent Number := 0;
V_Weekly_Absent Number := 0;
V_OTR Number;
V_OTR Number;
V_OTS Number;
V_OTS Number;
V_OTR_Approved Number;
V_OTR_Approved Number;
V_OTS_Approved Number;
V_OTS_Approved Number;
V_HAZARD_DAYS Number;
V_HAZARD_DAYS Number;
V_HAZARD_DAYS_HIGH Number;
V_HAZARD_DAYS_HIGH Number;
V_STAND_BY_DAYS Number;
V_STAND_BY_DAYS Number;
V_Period_Start Date;
V_Period_Start Date;
V_Period_End Date;
V_Period_End Date;
V_Counter_15_Minutes Number;
V_Counter_15_Minutes Number;
V_Counter_30_Minutes Number;
V_Counter_30_Minutes Number;
V_Counter_60_Minutes Number;
V_Counter_60_Minutes Number;
V_CUMULATIVE_LATE_HOURS Number;
V_CUMULATIVE_LATE_HOURS Number;
V_COUNTER_ABSENT_DAYS Number;
V_COUNTER_ABSENT_DAYS Number;
V_Counter_Leave_Days Number;
V_Counter_Leave_Days Number;
V_Date_Type Varchar2(20);
V_Date_Type Varchar2(20);
V_BBreak_In Date;
V_BBreak_In Date;
V_BBreak_Out Date;
V_BBreak_Out Date;
V_ABreak_In Date;
V_ABreak_In Date;
V_ABreak_Out Date;
V_ABreak_Out Date;
V_Shift_Name Varchar2(80);
V_Shift_Name Varchar2(80);
V_Attendanc_Type Varchar2(80);
V_Attendanc_Type Varchar2(80);
V_Yesterday_Date_Type Varchar2(20);
V_Yesterday_Date_Type Varchar2(20);
V_Yesterday_BBreak_In Date;
V_Yesterday_BBreak_In Date;
V_Yesterday_BBreak_Out Date;
V_Yesterday_BBreak_Out Date;
V_Yesterday_ABreak_In Date;
V_Yesterday_ABreak_In Date;
V_Yesterday_ABreak_Out Date;
V_Yesterday_ABreak_Out Date;
V_Yesterday_Shift_Name Varchar2(80);
V_Yesterday_Shift_Name Varchar2(80);
V_Yesterday_Attendanc_Type Varchar2(80);
V_Yesterday_Attendanc_Type Varchar2(80);
V_Leave_Type Varchar2(80);
V_Leave_Type Varchar2(80);
V_Excuse_Status Varchar2(1);
V_Excuse_Status Varchar2(1);
V_update_yesterday_record Boolean := False;
V_update_yesterday_record Boolean := False;
V_RecordCount Number := 0;
V_RecordCount Number := 0;
V_LastRecord Varchar2(5) := 'False';
V_LastRecord Varchar2(5) := 'False';
V_Late_Start Number;
V_Late_Start Number;
V_Counter_Flag Varchar2(5) := 'False';
V_Counter_Flag Varchar2(5) := 'False';
V_Plan_Flag Varchar2(5) := 'False';
V_Plan_Flag Varchar2(5) := 'False';
V_Plan_Flag1 Varchar2(5) := 'False';
V_Plan_Flag1 Varchar2(5) := 'False';
V_DualShift_Record Varchar2(5) := 'False';
V_DualShift_Record Varchar2(5) := 'False';
V_OverTime_Deduct Number := 0;
V_OverTime_Deduct Number := 0;
V_Action_Recs Number := 0;
V_Action_Recs Number := 0;
V_Distinct_Attendance_Recs Number := 0;
V_Distinct_Attendance_Recs Number := 0;
V_Attendance_Recs Number := 0;
V_Attendance_Recs Number := 0;
V_Yesterday_record_count number :=0;
V_Yesterday_record_count number :=0;
v_yesterday_record varchar2(5) :='False';
v_yesterday_record varchar2(5) :='False';
V_PREVIOUS_DAY_GENERATED varchar2(5) :='False';
V_PREVIOUS_DAY_GENERATED varchar2(5) :='False';
Cursor emp_list is
Cursor emp_list is
Select a.employee_number emp_no, a.person_id, Organization_Id, a.Business_Group_id
Select a.employee_number emp_no, a.person_id, Organization_Id, a.Business_Group_id
from PER_ALL_PEOPLE_F a, per_person_types b, PER_ALL_ASSIGNMENTS_F c
from PER_ALL_PEOPLE_F a, per_person_types b, PER_ALL_ASSIGNMENTS_F c
where a.person_type_id = b.person_type_id and
where a.person_type_id = b.person_type_id and
a.person_id = c.person_id and
a.person_id = c.person_id and
-- a.employee_number != '1237' and --to solve 14-feb-07 problem (subaiheen)
-- a.employee_number != '1237' and --to solve 14-feb-07 problem (subaiheen)
a.business_group_id = c.business_group_id and
a.business_group_id = c.business_group_id and
b.system_person_type = 'EMP' and
b.system_person_type = 'EMP' and
nvl(c.PRIMARY_FLAG,'Y') = 'Y' and
nvl(c.PRIMARY_FLAG,'Y') = 'Y' and
v_pdate between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE and
v_pdate between a.EFFECTIVE_START_DATE and a.EFFECTIVE_END_DATE and
v_pdate between c.EFFECTIVE_START_DATE and c.EFFECTIVE_END_DATE and
v_pdate between c.EFFECTIVE_START_DATE and c.EFFECTIVE_END_DATE and
(
(
(upper(replace(a.employee_number,'-','')) = upper(replace(P_EMP_NO,'-','')) and P_EMP_NO is not null)
(upper(replace(a.employee_number,'-','')) = upper(replace(P_EMP_NO,'-','')) and P_EMP_NO is not null)
OR
OR
(a.employee_number IS NOT Null and P_EMP_NO is NULL)
(a.employee_number IS NOT Null and P_EMP_NO is NULL)
); --if P_EMP_NO is null, then will work for all employees
); --if P_EMP_NO is null, then will work for all employees
BEGIN
BEGIN
Begin
Begin
select 'True' into V_PREVIOUS_DAY_GENERATED
select 'True' into V_PREVIOUS_DAY_GENERATED
from HXT_ATTENDANCE_AEC
from HXT_ATTENDANCE_AEC
where TRUNC(DATE_TIME_OUT)=V_PDATE-1 and rownum=1;
where TRUNC(DATE_TIME_OUT)=V_PDATE-1 and rownum=1;
Exception
Exception
When others then V_PREVIOUS_DAY_GENERATED:='False';
When others then V_PREVIOUS_DAY_GENERATED:='False';
end;
end;
if V_PREVIOUS_DAY_GENERATED='False' then --generate today only if yesterday is generated
if V_PREVIOUS_DAY_GENERATED='False' then --generate today only if yesterday is generated
FND_FILE.PUT_LINE(FND_FILE.LOG,' ERROR: Can not generate this day attendance while previous day is not generated');
FND_FILE.PUT_LINE(FND_FILE.LOG,' ERROR: Can not generate this day attendance while previous day is not generated');
RAISE_APPLICATION_ERROR(-20101, 'ERROR: Can not generate this day attendance while previous day is not generated');
RAISE_APPLICATION_ERROR(-20101, 'ERROR: Can not generate this day attendance while previous day is not generated');
end if;
end if;
-- it will load actionss from APACS system, if P_EMP_NO is null, then will work for all employees
-- it will load actionss from APACS system, if P_EMP_NO is null, then will work for all employees
HXT_TIME_ATTENDANCE_AEC.LOAD_ACTION(V_PDate,P_EMP_NO);
HXT_TIME_ATTENDANCE_AEC.LOAD_ACTION(V_PDate,P_EMP_NO);
-- it will get the start and end date for the period in which V_PDate will Fall
-- it will get the start and end date for the period in which V_PDate will Fall
HXT_TIME_ATTENDANCE_AEC.TIME_ATTENDANCE_PERIOD(V_PDate, V_Period_Start, V_Period_End);
HXT_TIME_ATTENDANCE_AEC.TIME_ATTENDANCE_PERIOD(V_PDate, V_Period_Start, V_Period_End);
--V_Period_Start:=NULL; --Temporay solution 28-Nov-2006 !!
--V_Period_Start:=NULL; --Temporay solution 28-Nov-2006 !!
--V_Period_End:=NULL; --Temporay solution 28-Nov-2006 !!
--V_Period_End:=NULL; --Temporay solution 28-Nov-2006 !!
-- This will update action table with the temporary card number to actual employee number
-- This will update action table with the temporary card number to actual employee number
HXT_TIME_ATTENDANCE_AEC.UPDATE_TEMPORARY_CARDS(V_PDate, P_Emp_No);
HXT_TIME_ATTENDANCE_AEC.UPDATE_TEMPORARY_CARDS(V_PDate, P_Emp_No);
For i In Emp_List Loop
For i In Emp_List Loop
FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Records for EMP# ' ||i.EMP_NO);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Records for EMP# ' ||i.EMP_NO);
--:emp_no := i.Person_id || ' - '||i.Emp_no;
--:emp_no := i.Person_id || ' - '||i.Emp_no;
--synchronize;
--synchronize;
V_Shift_Name := Null;
V_Shift_Name := Null;
V_Counter_Flag := 'False';
V_Counter_Flag := 'False';
V_Plan_Flag := 'False';
V_Plan_Flag := 'False';
V_Plan_Flag1 := 'False';
V_Plan_Flag1 := 'False';
-- This Function Will return the type of the date in which current day falls.
-- This Function Will return the type of the date in which current day falls.
-- 'Ramdan' date or 'Normal' date taking into account emp relgion
-- 'Ramdan' date or 'Normal' date taking into account emp relgion
V_Date_Type := HXT_TIME_ATTENDANCE_AEC.GET_DATE_TYPE(i.person_id,V_PDate);
V_Date_Type := HXT_TIME_ATTENDANCE_AEC.GET_DATE_TYPE(i.person_id,V_PDate);
-- To get the Employees Current Shift Timings i.e timings
-- To get the Employees Current Shift Timings i.e timings
-- Before Break In , Before Break Out, After Break In, After Break Out
-- Before Break In , Before Break Out, After Break In, After Break Out
HXT_TIME_ATTENDANCE_AEC.GET_EMPLOYEE_SHIFT_TIME (i.Person_ID, V_PDate, V_Date_Type,
HXT_TIME_ATTENDANCE_AEC.GET_EMPLOYEE_SHIFT_TIME (i.Person_ID, V_PDate, V_Date_Type,
V_BBreak_In, V_BBreak_Out, V_ABreak_In, V_ABreak_Out, V_Attendanc_Type,
V_BBreak_In, V_BBreak_Out, V_ABreak_In, V_ABreak_Out, V_Attendanc_Type,
V_Shift_Name);
V_Shift_Name);
If V_Shift_Name Is Not Null Then
If V_Shift_Name Is Not Null Then
-- This will get the time in and time out into a table HXT_aciton_RECS_AEC
-- This will get the time in and time out into a table HXT_aciton_RECS_AEC
-- and update the Action Table Flag to Two for those Records which fall in two days
-- and update the Action Table Flag to Two for those Records which fall in two days
/*HXT_TIME_ATTENDANCE_AEC.GET_DATE_TIME_IN_OUT(V_PDate, i.Emp_No, i.Person_Id,
/*HXT_TIME_ATTENDANCE_AEC.GET_DATE_TIME_IN_OUT(V_PDate, i.Emp_No, i.Person_Id,
V_BBreak_In, V_ABreak_Out,
V_BBreak_In, V_ABreak_Out,
V_Date_Time_In, V_Date_Time_Out, V_Yesterday_Record_Flag,
V_Date_Time_In, V_Date_Time_Out, V_Yesterday_Record_Flag,
V_DualShift_Record);*/
V_DualShift_Record);*/
-- new procedure by suhaibani
-- new procedure by suhaibani
HXT_TIME_ATTENDANCE_AEC.GET_DATE_TIME_IN_OUT(V_PDate, i.Emp_No, i.Person_Id,V_BBreak_In,V_update_yesterday_record);
HXT_TIME_ATTENDANCE_AEC.GET_DATE_TIME_IN_OUT(V_PDate, i.Emp_No, i.Person_Id,V_BBreak_In,V_update_yesterday_record);
Begin
Begin
-- to check that employee come or not at all
-- to check that employee come or not at all
Select Nvl(Count(*),0) Into V_RecordCount
Select Nvl(Count(*),0) Into V_RecordCount
From HXT_Action_RECS_AEC
From HXT_Action_RECS_AEC
Where Proc_Date between Trunc(V_PDate) and to_date(to_char(V_PDate,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS')
Where Proc_Date between Trunc(V_PDate) and to_date(to_char(V_PDate,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS')
And Emp_No = i.Emp_No;
And Emp_No = i.Emp_No;
End;
End;
-- If Employee only Dual Shift Record Exist Then for the perticular Process date no record will enter
-- If Employee only Dual Shift Record Exist Then for the perticular Process date no record will enter
-- and next day the dual shift record will be complted and inserted into HXT_ATTENDANCE_AEC
-- and next day the dual shift record will be complted and inserted into HXT_ATTENDANCE_AEC
-- If V_DualShift_Record = 'True' And Nvl(V_RecordCount,0) = 0 Then -- not needed by Suhaibani
-- If V_DualShift_Record = 'True' And Nvl(V_RecordCount,0) = 0 Then -- not needed by Suhaibani
-- Null; -- not needed by Suhaibani
-- Null; -- not needed by Suhaibani
-- Else -- not needed by Suhaibani
-- Else -- not needed by Suhaibani
Declare
Declare
-- this cursor will get the time in and out from HXT_ACTION_RECS_AEC and will do further procesing
-- this cursor will get the time in and out from HXT_ACTION_RECS_AEC and will do further procesing
-- and finally will insert it into HXT_ATTENDANCE_AEC;
-- and finally will insert it into HXT_ATTENDANCE_AEC;
Cursor In_Out is
Cursor In_Out is
Select * from HXT_Action_Recs_AEC
Select * from HXT_Action_Recs_AEC
Where Proc_Date between Trunc(V_PDate) and to_date(to_char(V_PDate,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS')
Where Proc_Date between Trunc(V_PDate) and to_date(to_char(V_PDate,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS')
And Emp_No = i.Emp_No
And Emp_No = i.Emp_No
Order By Date_Time_In;
Order By Date_Time_In;
In_Out_Rec In_Out%RowType;
In_Out_Rec In_Out%RowType;
Begin
Begin
-- Opening the Cursor which will call different procedure and functions to calculate
-- Opening the Cursor which will call different procedure and functions to calculate
-- tardiness, absentism, overtime, standby days, hazardous high, low and counters
-- tardiness, absentism, overtime, standby days, hazardous high, low and counters
Open In_Out;
Open In_Out;
Loop
Loop
Fetch In_Out InTo In_Out_Rec;
Fetch In_Out InTo In_Out_Rec;
If V_RecordCount = Nvl(In_Out%RowCount,0) Then
If V_RecordCount = Nvl(In_Out%RowCount,0) Then
V_LastRecord := 'True';
V_LastRecord := 'True';
Else
Else
V_LastRecord := 'False';
V_LastRecord := 'False';
End If;
End If;
If Nvl(In_Out%Rowcount,0) = 0 and (In_Out%NotFound Or In_Out%NotFound is null) Then
If Nvl(In_Out%Rowcount,0) = 0 and (In_Out%NotFound Or In_Out%NotFound is null) Then
null;
null;
Else
Else
Exit When Nvl(In_Out%Rowcount,0) > 0 And (In_Out%NotFound Or In_Out%NotFound is null);
Exit When Nvl(In_Out%Rowcount,0) > 0 And (In_Out%NotFound Or In_Out%NotFound is null);
V_Date_Time_In := In_Out_Rec.Date_Time_In;
V_Date_Time_In := In_Out_Rec.Date_Time_In;
V_Date_Time_Out := In_Out_Rec.Date_Time_Out;
V_Date_Time_Out := In_Out_Rec.Date_Time_Out;
End If;
End If;
-- will get the tardiness and 1/2 day absentism of the employee
-- will get the tardiness and 1/2 day absentism of the employee
if trunc(V_DATE_TIME_OUT)=trunc(V_PDATE) then
if trunc(V_DATE_TIME_OUT)=trunc(V_PDATE) then
V_Yesterday_record:='False'; -- need in insert statments
V_Yesterday_record:='False'; -- need in insert statments
HXT_TIME_ATTENDANCE_AEC.GET_LATE_TIME (V_PDate, V_BBreak_In, V_BBreak_Out,
HXT_TIME_ATTENDANCE_AEC.GET_LATE_TIME (V_PDate, V_BBreak_In, V_BBreak_Out,
V_ABreak_In, V_ABreak_Out, V_Date_Time_In, V_Date_Time_Out,
V_ABreak_In, V_ABreak_Out, V_Date_Time_In, V_Date_Time_Out,
V_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness,
V_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness,
V_Tardiness_Late, V_Attendanc_Type,
V_Tardiness_Late, V_Attendanc_Type,
V_Late_Start, V_OverTime_Deduct);
V_Late_Start, V_OverTime_Deduct);
-- will maintint the counters 15 minute lated etc, leave , absentism etc.
-- will maintint the counters 15 minute lated etc, leave , absentism etc.
HXT_TIME_ATTENDANCE_AEC.GET_COUNTER_INFO (i.Person_ID, V_PDate, V_Tardiness,
HXT_TIME_ATTENDANCE_AEC.GET_COUNTER_INFO (i.Person_ID, V_PDate, V_Tardiness,
V_Todays_Absent, V_Period_Start, V_Period_End,
V_Todays_Absent, V_Period_Start, V_Period_End,
V_Counter_15_Minutes, V_Counter_30_Minutes,
V_Counter_15_Minutes, V_Counter_30_Minutes,
V_Counter_60_Minutes, V_CUMULATIVE_LATE_HOURS, V_Excuse_Status,
V_Counter_60_Minutes, V_CUMULATIVE_LATE_HOURS, V_Excuse_Status,
V_COUNTER_ABSENT_DAYS, V_Counter_Leave_Days, V_Leave_Type,
V_COUNTER_ABSENT_DAYS, V_Counter_Leave_Days, V_Leave_Type,
V_DATE_TIME_IN, V_Official_Work_Hours, V_Shift_Name,
V_DATE_TIME_IN, V_Official_Work_Hours, V_Shift_Name,
V_Attendanc_Type, V_LastRecord,
V_Attendanc_Type, V_LastRecord,
V_Late_Start, V_Counter_Flag);
V_Late_Start, V_Counter_Flag);
-- will get the overtime, standby day, hazardious low and high again the submit plan --moved by hadba
-- will get the overtime, standby day, hazardious low and high again the submit plan
--moved by hadba 25/7/2013
HXT_TIME_ATTENDANCE_AEC.GET_PLAN_DETAILS (V_PDate, i.Person_ID, V_Date_Type,
HXT_TIME_ATTENDANCE_AEC.GET_PLAN_DETAILS (V_PDate, i.Person_ID, V_Date_Type,
V_Official_Work_Hours, V_Total_Work_Hours,
V_Official_Work_Hours, V_Total_Work_Hours,
V_OTR, V_OTS,
V_OTR, V_OTS,
V_OTR_Approved, V_OTS_Approved,
V_OTR_Approved, V_OTS_Approved,
V_Hazard_Days,V_HAZARD_DAYS_HIGH, V_Stand_By_Days,
V_Hazard_Days,V_HAZARD_DAYS_HIGH, V_Stand_By_Days,
V_Attendanc_Type, V_LastRecord,
V_Attendanc_Type, V_LastRecord,
V_Plan_Flag, V_Plan_Flag1, V_OverTime_Deduct, V_Leave_Type);
V_Plan_Flag, V_Plan_Flag1, V_OverTime_Deduct, V_Leave_Type);
else
else
V_Yesterday_Date_Type := HXT_TIME_ATTENDANCE_AEC.GET_DATE_TYPE (i.person_id,V_PDate-1);
V_Yesterday_Date_Type := HXT_TIME_ATTENDANCE_AEC.GET_DATE_TYPE (i.person_id,V_PDate-1);
HXT_TIME_ATTENDANCE_AEC.GET_EMPLOYEE_SHIFT_TIME (i.Person_ID, V_PDate-1, V_Yesterday_Date_Type,
HXT_TIME_ATTENDANCE_AEC.GET_EMPLOYEE_SHIFT_TIME (i.Person_ID, V_PDate-1, V_Yesterday_Date_Type,
V_Yesterday_BBreak_In, V_Yesterday_BBreak_Out, V_Yesterday_ABreak_In, V_Yesterday_ABreak_Out, V_Yesterday_Attendanc_Type,
V_Yesterday_BBreak_In, V_Yesterday_BBreak_Out, V_Yesterday_ABreak_In, V_Yesterday_ABreak_Out, V_Yesterday_Attendanc_Type,
V_Yesterday_Shift_Name);
V_Yesterday_Shift_Name);
HXT_TIME_ATTENDANCE_AEC.GET_LATE_TIME (V_PDate-1, V_Yesterday_BBreak_In, V_Yesterday_BBreak_Out,
HXT_TIME_ATTENDANCE_AEC.GET_LATE_TIME (V_PDate-1, V_Yesterday_BBreak_In, V_Yesterday_BBreak_Out,
V_Yesterday_ABreak_In, V_Yesterday_ABreak_Out, V_Date_Time_In, V_Date_Time_Out,
V_Yesterday_ABreak_In, V_Yesterday_ABreak_Out, V_Date_Time_In, V_Date_Time_Out,
V_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness, V_Tardiness_Late, V_Attendanc_Type,
V_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness, V_Tardiness_Late, V_Attendanc_Type,
V_Late_Start, V_OverTime_Deduct);
V_Late_Start, V_OverTime_Deduct);
-- will maintint the counters 15 minute lated etc, leave , absentism etc.
-- will maintint the counters 15 minute lated etc, leave , absentism etc.
HXT_TIME_ATTENDANCE_AEC.GET_COUNTER_INFO (i.Person_ID, V_PDate-1, V_Tardiness,
HXT_TIME_ATTENDANCE_AEC.GET_COUNTER_INFO (i.Person_ID, V_PDate-1, V_Tardiness,
V_Todays_Absent, V_Period_Start, V_Period_End,
V_Todays_Absent, V_Period_Start, V_Period_End,
V_Counter_15_Minutes, V_Counter_30_Minutes,
V_Counter_15_Minutes, V_Counter_30_Minutes,
V_Counter_60_Minutes, V_CUMULATIVE_LATE_HOURS, V_Excuse_Status,
V_Counter_60_Minutes, V_CUMULATIVE_LATE_HOURS, V_Excuse_Status,
V_COUNTER_ABSENT_DAYS, V_Counter_Leave_Days, V_Leave_Type,
V_COUNTER_ABSENT_DAYS, V_Counter_Leave_Days, V_Leave_Type,
V_DATE_TIME_IN, V_Official_Work_Hours, V_Yesterday_Shift_Name,
V_DATE_TIME_IN, V_Official_Work_Hours, V_Yesterday_Shift_Name,
V_Yesterday_Attendanc_Type, V_LastRecord,
V_Yesterday_Attendanc_Type, V_LastRecord,
V_Late_Start, V_Counter_Flag);
V_Late_Start, V_Counter_Flag);
-- will get the overtime, standby day, hazardious low and high again the submit plan --moved by hadba
-- will get the overtime, standby day, hazardious low and high again the submit plan
HXT_TIME_ATTENDANCE_AEC.GET_PLAN_DETAILS (V_PDate-1, i.Person_ID, V_Yesterday_Date_Type,
--moved by hadba 25/7/2013
HXT_TIME_ATTENDANCE_AEC.GET_PLAN_DETAILS (V_PDate, i.Person_ID, V_Date_Type,
V_Official_Work_Hours, V_Total_Work_Hours,
V_Official_Work_Hours, V_Total_Work_Hours,
V_OTR, V_OTS,
V_OTR, V_OTS,
V_OTR_Approved, V_OTS_Approved,
V_OTR_Approved, V_OTS_Approved,
V_Hazard_Days,V_HAZARD_DAYS_HIGH, V_Stand_By_Days,
V_Hazard_Days,V_HAZARD_DAYS_HIGH, V_Stand_By_Days,
V_Attendanc_Type, V_LastRecord,
V_Attendanc_Type, V_LastRecord,
V_Plan_Flag, V_Plan_Flag1, V_OverTime_Deduct, V_Leave_Type);
V_Plan_Flag, V_Plan_Flag1, V_OverTime_Deduct, V_Leave_Type);
V_Yesterday_record:='True'; -- need in insert statments
V_Yesterday_record:='True'; -- need in insert statments
V_Counter_Flag:='False'; -- Added by suhaibani 25-06-08 to solve problem of not counting for today records in case yesterday records was updated.
V_Counter_Flag:='False'; -- Added by suhaibani 25-06-08 to solve problem of not counting for today records in case yesterday records was updated.
end if;
end if;
If V_update_yesterday_record Then
If V_update_yesterday_record Then
-- if today is also found same record then previous day record will be deleted
-- if today is also found same record then previous day record will be deleted
Delete HXT_ATTENDANCE_AEC Where Person_Id = i.Person_Id and (Date_time_in = v_date_time_in OR Date_time_in = trunc(V_PDate-1));
Delete HXT_ATTENDANCE_AEC Where Person_Id = i.Person_Id and (Date_time_in = v_date_time_in OR Date_time_in = trunc(V_PDate-1));
End If;
End If;
--If In_Out_Rec.Record_Status is Not Null Then
--If In_Out_Rec.Record_Status is Not Null Then
-- now all the required inofomation is gathered and calculated. it will be inserted into HXT_ATTENDANCE_AEC table
-- now all the required inofomation is gathered and calculated. it will be inserted into HXT_ATTENDANCE_AEC table
--dbms_output.put_line(to_char(V_DATE_TIME_IN,'DD HH24:MI'));
--dbms_output.put_line(to_char(V_DATE_TIME_IN,'DD HH24:MI'));
BEGIN
BEGIN
insert into HXT_ATTENDANCE_AEC
insert into HXT_ATTENDANCE_AEC
(BUSINESS_GROUP_ID , ORGANIZATION_ID,
(BUSINESS_GROUP_ID , ORGANIZATION_ID,
PERSON_ID, DATE_TIME_IN, DATE_TIME_OUT, SHIFT_TIME_IN, SHIFT_TIME_OUT, OFFICIAL_WORK_HOURS, TOTAL_WORK_HOURS,
PERSON_ID, DATE_TIME_IN, DATE_TIME_OUT, SHIFT_TIME_IN, SHIFT_TIME_OUT, OFFICIAL_WORK_HOURS, TOTAL_WORK_HOURS,
REGULAR_OVERTIME_SUGGESTED, REGULAR_OVERTIME_APPROVED, REGULAR_OVERTIME_ACTUAL, SPECIAL_OVERTIME_SUGGESTED, SPECIAL_OVERTIME_APPROVED, SPECIAL_OVERTIME_ACTUAL, HAZARD_DAYS, HAZARD_DAYS_HIGH , STAND_BY_DAYS,
REGULAR_OVERTIME_SUGGESTED, REGULAR_OVERTIME_APPROVED, REGULAR_OVERTIME_ACTUAL, SPECIAL_OVERTIME_SUGGESTED, SPECIAL_OVERTIME_APPROVED, SPECIAL_OVERTIME_ACTUAL, HAZARD_DAYS, HAZARD_DAYS_HIGH , STAND_BY_DAYS,
COUNTER_15_MINUTES , COUNTER_30_MINUTES , COUNTER_60_MINUTES , COUNTER_ABSENT_DAYS , COUNTER_LEAVE_DAYS , CUMULATIVE_LATE_HOURS,
COUNTER_15_MINUTES , COUNTER_30_MINUTES , COUNTER_60_MINUTES , COUNTER_ABSENT_DAYS , COUNTER_LEAVE_DAYS , CUMULATIVE_LATE_HOURS,
SHIFT_TYPE, STATUS, POSTED_STATUS, EXCUSE_STATUS,
SHIFT_TYPE, STATUS, POSTED_STATUS, EXCUSE_STATUS,
RECORD_STATUS, Remarks,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY) -- added creation date suhaibani
RECORD_STATUS, Remarks,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY) -- added creation date suhaibani
VALUES
VALUES
(i.Business_Group_Id, i.Organization_Id,
(i.Business_Group_Id, i.Organization_Id,
i.Person_Id, V_Date_Time_In, V_Date_Time_Out, decode(V_yesterday_record,'True',V_Yesterday_BBreak_In,V_BBreak_In), decode(V_yesterday_record,'True',V_Yesterday_ABreak_Out,V_ABreak_Out), V_Official_Work_Hours, V_Total_Work_Hours,
i.Person_Id, V_Date_Time_In, V_Date_Time_Out, decode(V_yesterday_record,'True',V_Yesterday_BBreak_In,V_BBreak_In), decode(V_yesterday_record,'True',V_Yesterday_ABreak_Out,V_ABreak_Out), V_Official_Work_Hours, V_Total_Work_Hours,
V_OTR , Nvl(V_OTR_Approved,0) , Nvl(V_OTR_Approved,0) , V_OTS ,Nvl(V_OTS_Approved,0) , Nvl(V_OTS_Approved,0) ,V_HAZARD_DAYS, V_HAZARD_DAYS_HIGH, V_STAND_BY_DAYS,
V_OTR , Nvl(V_OTR_Approved,0) , Nvl(V_OTR_Approved,0) , V_OTS ,Nvl(V_OTS_Approved,0) , Nvl(V_OTS_Approved,0) ,V_HAZARD_DAYS, V_HAZARD_DAYS_HIGH, V_STAND_BY_DAYS,
Nvl(V_COUNTER_15_MINUTES,0), Nvl(V_COUNTER_30_MINUTES,0), Nvl(V_COUNTER_60_MINUTES,0), Nvl(V_COUNTER_ABSENT_DAYS,0), NVL(V_Counter_Leave_Days,0), NVL(V_CUMULATIVE_LATE_HOURS,0),
Nvl(V_COUNTER_15_MINUTES,0), Nvl(V_COUNTER_30_MINUTES,0), Nvl(V_COUNTER_60_MINUTES,0), Nvl(V_COUNTER_ABSENT_DAYS,0), NVL(V_Counter_Leave_Days,0), NVL(V_CUMULATIVE_LATE_HOURS,0),
decode(V_yesterday_record,'True',V_Yesterday_Shift_Name,V_Shift_Name), V_Leave_Type, 0, V_Excuse_Status,
decode(V_yesterday_record,'True',V_Yesterday_Shift_Name,V_Shift_Name), V_Leave_Type, 0, V_Excuse_Status,
DECODE(Substr(In_Out_Rec.Record_Status,1,5),'Error',1,0), DECODE(In_Out_Rec.Record_Status,'Error1','SYSTEM MSG: Inconsistent Attendance (IN without OUT!)','Error2','SYSTEM MSG: Inconsistent Attendance (Long stay!)','Error3','SYSTEM MSG: Inconsistent Attendance (OUT without IN!)'),sysdate,3,sysdate,3);
DECODE(Substr(In_Out_Rec.Record_Status,1,5),'Error',1,0), DECODE(In_Out_Rec.Record_Status,'Error1','SYSTEM MSG: Inconsistent Attendance (IN without OUT!)','Error2','SYSTEM MSG: Inconsistent Attendance (Long stay!)','Error3','SYSTEM MSG: Inconsistent Attendance (OUT without IN!)'),sysdate,3,sysdate,3);
exception
exception
when DUP_VAL_ON_INDEX then null;
when DUP_VAL_ON_INDEX then null;
END;
END;
Exit When Nvl(In_Out%Rowcount,0) = 0 and (In_Out%NotFound Or In_Out%NotFound is null);
Exit When Nvl(In_Out%Rowcount,0) = 0 and (In_Out%NotFound Or In_Out%NotFound is null);
End Loop;
End Loop;
Close IN_OUT;
Close IN_OUT;
if V_update_yesterday_record then
if V_update_yesterday_record then
select count(*) into v_yesterday_record_count
select count(*) into v_yesterday_record_count
from HXT_ATTENDANCE_AEC
from HXT_ATTENDANCE_AEC
where PERSON_ID=i.Person_ID and trunc(DATE_TIME_OUT)=trunc(V_PDATE-1);
where PERSON_ID=i.Person_ID and trunc(DATE_TIME_OUT)=trunc(V_PDATE-1);
if nvl(v_yesterday_record_count,0)=0 then -- insert blank record for yestedat, it was deleted above
if nvl(v_yesterday_record_count,0)=0 then -- insert blank record for yestedat, it was deleted above
V_Date_Time_In := Trunc(V_PDate-1);
V_Date_Time_In := Trunc(V_PDate-1);
V_Date_Time_Out := Trunc(V_PDate-1);
V_Date_Time_Out := Trunc(V_PDate-1);
HXT_TIME_ATTENDANCE_AEC.GET_EMPLOYEE_SHIFT_TIME (i.Person_ID, V_PDate-1, V_Date_Type,
HXT_TIME_ATTENDANCE_AEC.GET_EMPLOYEE_SHIFT_TIME (i.Person_ID, V_PDate-1, V_Date_Type,
V_BBreak_In, V_BBreak_Out, V_ABreak_In, V_ABreak_Out, V_Attendanc_Type,
V_BBreak_In, V_BBreak_Out, V_ABreak_In, V_ABreak_Out, V_Attendanc_Type,
V_Shift_Name);
V_Shift_Name);
HXT_TIME_ATTENDANCE_AEC.GET_LATE_TIME (V_PDate-1, V_BBreak_In, V_BBreak_Out,
HXT_TIME_ATTENDANCE_AEC.GET_LATE_TIME (V_PDate-1, V_BBreak_In, V_BBreak_Out,
V_ABreak_In, V_ABreak_Out, V_Date_Time_In, V_Date_Time_Out,
V_ABreak_In, V_ABreak_Out, V_Date_Time_In, V_Date_Time_Out,
V_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness,
V_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness,
V_Tardiness_Late, V_Attendanc_Type,
V_Tardiness_Late, V_Attendanc_Type,
V_Late_Start, V_OverTime_Deduct);
V_Late_Start, V_OverTime_Deduct);
-- will maintint the counters 15 minute lated etc, leave , absentism etc.
-- will maintint the counters 15 minute lated etc, leave , absentism etc.
HXT_TIME_ATTENDANCE_AEC.GET_COUNTER_INFO (i.Person_ID, V_PDate-1, V_Tardiness, V_Todays_Absent, V_Period_Start, V_Period_End,
HXT_TIME_ATTENDANCE_AEC.GET_COUNTER_INFO (i.Person_ID, V_PDate-1, V_Tardiness, V_Todays_Absent, V_Period_Start, V_Period_End,
V_Counter_15_Minutes, V_Counter_30_Minutes, V_Counter_60_Minutes, V_CUMULATIVE_LATE_HOURS, V_Excuse_Status,
V_Counter_15_Minutes, V_Counter_30_Minutes, V_Counter_60_Minutes, V_CUMULATIVE_LATE_HOURS, V_Excuse_Status,
V_COUNTER_ABSENT_DAYS, V_Counter_Leave_Days, V_Leave_Type, V_DATE_TIME_IN, V_Official_Work_Hours, V_Shift_Name,
V_COUNTER_ABSENT_DAYS, V_Counter_Leave_Days, V_Leave_Type, V_DATE_TIME_IN, V_Official_Work_Hours, V_Shift_Name,
V_Attendanc_Type, V_LastRecord, V_Late_Start, V_Counter_Flag);
V_Attendanc_Type, V_LastRecord, V_Late_Start, V_Counter_Flag);
-- will get the overtime, standby day, hazardious low and high again the submit plan -- moved by hadba
HXT_TIME_ATTENDANCE_AEC.GET_PLAN_DETAILS (V_PDate-1, i.Person_ID, V_Date_Type, V_Official_Work_Hours, V_Total_Work_Hours,
V_OTR, V_OTS, V_OTR_Approved, V_OTS_Approved, V_Hazard_Days,V_HAZARD_DAYS_HIGH, V_Stand_By_Days,
V_Attendanc_Type, V_LastRecord, V_Plan_Flag, V_Plan_Flag1, V_OverTime_Deduct, V_Leave_Type);
insert into HXT_ATTENDANCE_AEC
(BUSINESS_GROUP_ID , ORGANIZATION_ID, PERSON_ID, DATE_TIME_IN, DATE_TIME_OUT, SHIFT_TIME_IN, SHIFT_TIME_OUT, OFFICIAL_WORK_HOURS, TOTAL_WORK_HOURS,
REGULAR_OVERTIME_SUGGESTED, REGULAR_OVERTIME_APPROVED, REGULAR_OVERTIME_ACTUAL, SPECIAL_OVERTIME_SUGGESTED, SPECIAL_OVERTIME_APPROVED, SPECIAL_OVERTIME_ACTUAL, HAZARD_DAYS, HAZARD_DAYS_HIGH , STAND_BY_DAYS,
COUNTER_15_MINUTES, COUNTER_30_MINUTES, COUNTER_60_MINUTES , COUNTER_ABSENT_DAYS , COUNTER_LEAVE_DAYS , CUMULATIVE_LATE_HOURS,
SHIFT_TYPE, STATUS, POSTED_STATUS, EXCUSE_STATUS, RECORD_STATUS, Remarks,CREATION_DATE,CREATED_BY,LAST_UPDATE_DATE,LAST_UPDATED_BY)
VALUES
(i.Business_Group_Id, i.Organization_Id, i.Person_Id, V_Date_Time_In, V_Date_Time_Out, V_BBreak_In, V_ABreak_Out, V_Official_Work_Hours, V_Total_Work_Hours,
V_OTR, Nvl(V_OTR_Approved,0), Nvl(V_OTR_Approved,0), V_OTS ,Nvl(V_OTS_Approved,0) , Nvl(V_OTS_Approved,0) ,V_HAZARD_DAYS, V_HAZARD_DAYS_HIGH, V_STAND_BY_DAYS,
Nvl(V_COUNTER_15_MINUTES,0), Nvl(V_COUNTER_30_MINUTES,0), Nvl(V_COUNTER_60_MINUTES,0), Nvl(V_COUNTER_ABSENT_DAYS,0), NVL(V_Counter_Leave_Days,0), NVL(V_CUMULATIVE_LATE_HOURS,0),
V_Shift_Name, V_Leave_Type, 0, V_Excuse_Status,0, NULL,sysdate,3,sysdate,3);
end if;
end if;
End;
End If;
-- this will update the record if the leave is enterd after the process run for the current day
HXT_TIME_ATTENDANCE_AEC.Leave_Difference(i.Person_Id, V_Period_Start, V_Period_End);
-- this will update the record if the plan is enterd after the process run for the current day
HXT_TIME_ATTENDANCE_AEC.Submit_Plan_Check(i.Person_Id, V_Period_Start, V_Period_End);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Records for EMP# ' ||i.EMP_NO||' is '||V_RecordCount);
End Loop;
-- this will auto sign the attendance 5 days back records which are not signed
-- HXT_TIME_ATTENDANCE_AEC.Signe_Attendance(V_PDate); STOP BY SUHAIBANI No auto sign
-- The Folling 3 Statments will populate the log file
-- This Statment will count total number of Action table records processed in a perticular date
Begin
Select Count(*) Into V_Action_Recs
From HXT_ACTION_AEC Where Trunc(Date_Time)= V_PDate;
Exception
When Others Then Null;
End;
-- This Statment will count total number of employess records inserted in HXT_ATTENDANCE_TABLE
Begin
Select Count(Distinct Person_Id) Into V_Distinct_Attendance_Recs
From HXT_ATTENDANCE_AEC Where Trunc(Date_Time_In) = V_PDate;
Exception
When Others Then Null;
End;
-- This Statment will count total records inserted in HXT_ATTENDANCE_TABLE
Begin
Select Count(Person_Id) Into V_Attendance_Recs
From HXT_ATTENDANCE_AEC Where Trunc(Date_Time_In) = V_PDate;
Exception
When Others Then Null;
End;
-- Rebuid manager Hierarchy to refelect any new changes
if P_EMP_NO is NULL then -- to be run during daily attendance generation, not for individual generation.
hxt_time_attendance_aec.BUILD_MGR_DEPT_HIERARCHY(null,null,0);
end if;
-- maintain the log file
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Action Table Records Processed ' ||V_Action_Recs);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Employee Records in Attendance ' ||V_Distinct_Attendance_Recs);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Total Records Inserted in Attendance ' ||V_Attendance_Recs);
--COMMIT;
--Exception
--When Others Then message(sqlcode||sqlerrm);
END Gen_Attendance;
-- This Function Return The Type of Date i.e. the