Comparar texto

Encuentra la diferencia entre dos archivos de texto

Real-time diff

Unified diff

Collapse lines

Highlight change

Syntax highlighting

Herramientas

Diffchecker Desktop The most secure way to run Diffchecker. Get the Diffchecker Desktop app: your diffs never leave your computer!Get Desktop

Untitled diff

Created 10 years agoDiff never expires

Lines
Total
Removed

Words
Total
Removed

To continue using this feature, upgrade to Diffchecker Pro View Pricing

376 lines

Lines
Total
Added

Words
Total
Added

To continue using this feature, upgrade to Diffchecker Pro View Pricing

388 lines

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

-- of the 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

-- 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

-- As on the basis of date type employees shift type is selected from employee plan

-- Now get the Employees Current Shift Timings i.e

-- Before Break In , Before Break Out, After Break In, After Break Out in date format

-- DD-MON-YYYY HH24:MI.

-- 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

-- 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

-- dual days.

-- 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

-- no record will be enterd. and next day his current records and prevuios day dual day

-- record will be enterd in HXT_ATTENDANCE_AEC.

-- 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

-- in HXT_ATTENDANCE_AEC.

-- 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

-- 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

-- and some information is enterd in log file of the process

-- at the end of the loop

-- 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

-- Then in the last after all loops ends system will

-- auto sign the attendance for 5 days back records which are not signed

/*********************************************************************************/

/* CLEANINIG: CODE CLEANED OUT on 13-May-2007 By Suhaibani */

/* 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

/*Gen_Attendance is the main procedure which will run the loop for each employee

and will be calling the other procedures*/

V_PDate DATE := to_date(substr(P_date,1,10),'YYYY/MM/DD');

V_Date_Time_In DATE;

V_Date_Time_Out DATE;

V_Total_Work_Hours DATE;

V_Official_Work_Hours DATE;

V_Tardiness Number := 0;

V_Tardiness_Late Number := 0;

V_Todays_Absent Number := 0;

V_Weekly_Absent Number := 0;

V_OTR Number;

V_OTS Number;

V_OTR_Approved Number;

V_OTS_Approved Number;

V_HAZARD_DAYS Number;

V_HAZARD_DAYS_HIGH Number;

V_STAND_BY_DAYS Number;

V_Period_Start Date;

V_Period_End Date;

V_Counter_15_Minutes Number;

V_Counter_30_Minutes Number;

V_Counter_60_Minutes Number;

V_CUMULATIVE_LATE_HOURS Number;

V_COUNTER_ABSENT_DAYS Number;

V_Counter_Leave_Days Number;

V_Date_Type Varchar2(20);

V_BBreak_In Date;

V_BBreak_Out Date;

V_ABreak_In Date;

V_ABreak_Out Date;

V_Shift_Name Varchar2(80);

V_Attendanc_Type Varchar2(80);

V_Yesterday_Date_Type Varchar2(20);

V_Yesterday_BBreak_In Date;

V_Yesterday_BBreak_Out Date;

V_Yesterday_ABreak_In Date;

V_Yesterday_ABreak_Out Date;

V_Yesterday_Shift_Name Varchar2(80);

V_Yesterday_Attendanc_Type Varchar2(80);

V_Leave_Type Varchar2(80);

V_Excuse_Status Varchar2(1);

V_update_yesterday_record Boolean := False;

V_RecordCount Number := 0;

V_LastRecord Varchar2(5) := 'False';

V_Late_Start Number;

V_Counter_Flag Varchar2(5) := 'False';

V_Plan_Flag Varchar2(5) := 'False';

V_Plan_Flag1 Varchar2(5) := 'False';

V_DualShift_Record Varchar2(5) := 'False';

V_OverTime_Deduct Number := 0;

V_Action_Recs Number := 0;

V_Distinct_Attendance_Recs Number := 0;

V_Attendance_Recs Number := 0;

V_Yesterday_record_count number :=0;

v_yesterday_record varchar2(5) :='False';

V_PREVIOUS_DAY_GENERATED varchar2(5) :='False';

Cursor emp_list is

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

where a.person_type_id = b.person_type_id and

a.person_id = c.person_id and

-- a.employee_number != '1237' and --to solve 14-feb-07 problem (subaiheen)

a.business_group_id = c.business_group_id and

b.system_person_type = 'EMP' and

nvl(c.PRIMARY_FLAG,'Y') = 'Y' 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

(

(upper(replace(a.employee_number,'-','')) = upper(replace(P_EMP_NO,'-','')) and P_EMP_NO is not 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

BEGIN

Begin

select 'True' into V_PREVIOUS_DAY_GENERATED

from HXT_ATTENDANCE_AEC

where TRUNC(DATE_TIME_OUT)=V_PDATE-1 and rownum=1;

Exception

When others then V_PREVIOUS_DAY_GENERATED:='False';

end;

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');

RAISE_APPLICATION_ERROR(-20101, 'ERROR: Can not generate this day attendance while previous day is not generated');

end if;

-- 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);

-- 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);

--V_Period_Start:=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

HXT_TIME_ATTENDANCE_AEC.UPDATE_TEMPORARY_CARDS(V_PDate, P_Emp_No);

For i In Emp_List Loop

FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Records for EMP# ' ||i.EMP_NO);

--:emp_no := i.Person_id || ' - '||i.Emp_no;

--synchronize;

V_Shift_Name := Null;

V_Counter_Flag := 'False';

V_Plan_Flag := 'False';

V_Plan_Flag1 := 'False';

-- This Function Will return the type of the date in which current day falls.

-- '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);

-- To get the Employees Current Shift Timings i.e timings

-- 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,

V_BBreak_In, V_BBreak_Out, V_ABreak_In, V_ABreak_Out, V_Attendanc_Type,

V_Shift_Name);

If V_Shift_Name Is Not Null Then

-- 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

/*HXT_TIME_ATTENDANCE_AEC.GET_DATE_TIME_IN_OUT(V_PDate, i.Emp_No, i.Person_Id,

V_BBreak_In, V_ABreak_Out,

V_Date_Time_In, V_Date_Time_Out, V_Yesterday_Record_Flag,

V_DualShift_Record);*/

-- 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);

Begin

-- to check that employee come or not at all

Select Nvl(Count(*),0) Into V_RecordCount

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')

And Emp_No = i.Emp_No;

End;

-- 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

-- If V_DualShift_Record = 'True' And Nvl(V_RecordCount,0) = 0 Then -- not needed by Suhaibani

-- Null; -- not needed by Suhaibani

-- Else -- not needed by Suhaibani

Declare

-- 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;

Cursor In_Out is

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')

And Emp_No = i.Emp_No

Order By Date_Time_In;

In_Out_Rec In_Out%RowType;

Begin

-- Opening the Cursor which will call different procedure and functions to calculate

-- tardiness, absentism, overtime, standby days, hazardous high, low and counters

Open In_Out;

Loop

Fetch In_Out InTo In_Out_Rec;

If V_RecordCount = Nvl(In_Out%RowCount,0) Then

V_LastRecord := 'True';

Else

V_LastRecord := 'False';

End If;

If Nvl(In_Out%Rowcount,0) = 0 and (In_Out%NotFound Or In_Out%NotFound is null) Then

null;

Else

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_Out := In_Out_Rec.Date_Time_Out;

End If;

-- will get the tardiness and 1/2 day absentism of the employee

if trunc(V_DATE_TIME_OUT)=trunc(V_PDATE) then

V_Yesterday_record:='False'; -- need in insert statments

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_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness,

V_Tardiness_Late, V_Attendanc_Type,

V_Late_Start, V_OverTime_Deduct);

-- 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,

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_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);

-- 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,

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);

else

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,

V_Yesterday_BBreak_In, V_Yesterday_BBreak_Out, V_Yesterday_ABreak_In, V_Yesterday_ABreak_Out, V_Yesterday_Attendanc_Type,

V_Yesterday_Shift_Name);

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_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness, V_Tardiness_Late, V_Attendanc_Type,

V_Late_Start, V_OverTime_Deduct);

-- 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,

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_Yesterday_Shift_Name,

V_Yesterday_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

-- 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_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);

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.

end if;

If V_update_yesterday_record Then

-- 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));

End If;

--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

--dbms_output.put_line(to_char(V_DATE_TIME_IN,'DD HH24:MI'));

BEGIN

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) -- added creation date suhaibani

VALUES

(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,

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),

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);

exception

when DUP_VAL_ON_INDEX then null;

END;

Exit When Nvl(In_Out%Rowcount,0) = 0 and (In_Out%NotFound Or In_Out%NotFound is null);

End Loop;

Close IN_OUT;

if V_update_yesterday_record then

select count(*) into v_yesterday_record_count

from HXT_ATTENDANCE_AEC

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

V_Date_Time_In := 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,

V_BBreak_In, V_BBreak_Out, V_ABreak_In, V_ABreak_Out, V_Attendanc_Type,

V_Shift_Name);

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_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness,

V_Tardiness_Late, V_Attendanc_Type,

V_Late_Start, V_OverTime_Deduct);

-- 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,

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_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,

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;

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

Diffs guardadas

Texto original

Abrir archivo

CREATE OR REPLACE PACKAGE APPS."HXT_TIME_ATTENDANCE_AEC"  as
-- The main package of the generate attendance
     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
-- of the 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
-- 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
-- As on the basis of date type employees shift type is selected from employee plan
-- Now get the Employees Current Shift Timings i.e
-- Before Break In , Before Break Out, After Break In, After Break Out in date format
-- DD-MON-YYYY HH24:MI.
-- 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
-- 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
-- dual days.
-- 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
-- no record will be enterd. and next day his current records and prevuios day dual day
-- record will be enterd in HXT_ATTENDANCE_AEC.
-- 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
-- in HXT_ATTENDANCE_AEC.
-- 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
-- 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
-- and some information is enterd in log file of the process
-- at the end of the loop
-- 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
-- Then in the last after all loops ends system will
-- auto sign the attendance for 5 days back records which are not signed
/*********************************************************************************/
/* CLEANINIG: CODE CLEANED OUT on 13-May-2007 By Suhaibani          */
/* 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
/*Gen_Attendance is the main procedure which will run the loop for each employee
 and will be calling the other procedures*/
   V_PDate                 DATE := to_date(substr(P_date,1,10),'YYYY/MM/DD');
   V_Date_Time_In          DATE;
   V_Date_Time_Out         DATE;
   V_Total_Work_Hours      DATE;
   V_Official_Work_Hours   DATE;
   V_Tardiness             Number       := 0;
   V_Tardiness_Late        Number       := 0;
   V_Todays_Absent         Number       := 0;
   V_Weekly_Absent         Number       := 0;
   V_OTR                   Number;
   V_OTS                   Number;
   V_OTR_Approved          Number;
   V_OTS_Approved          Number;
   V_HAZARD_DAYS           Number;
   V_HAZARD_DAYS_HIGH      Number;
   V_STAND_BY_DAYS         Number;
   V_Period_Start          Date;
   V_Period_End            Date;
   V_Counter_15_Minutes    Number;
   V_Counter_30_Minutes    Number;
   V_Counter_60_Minutes    Number;
   V_CUMULATIVE_LATE_HOURS Number;
   V_COUNTER_ABSENT_DAYS   Number;
   V_Counter_Leave_Days    Number;
   V_Date_Type             Varchar2(20);
   V_BBreak_In             Date;
   V_BBreak_Out            Date;
   V_ABreak_In             Date;
   V_ABreak_Out            Date;
   V_Shift_Name            Varchar2(80);
   V_Attendanc_Type        Varchar2(80);
   V_Yesterday_Date_Type             Varchar2(20);
   V_Yesterday_BBreak_In             Date;
   V_Yesterday_BBreak_Out            Date;
   V_Yesterday_ABreak_In             Date;
   V_Yesterday_ABreak_Out            Date;
   V_Yesterday_Shift_Name            Varchar2(80);
   V_Yesterday_Attendanc_Type        Varchar2(80);
   V_Leave_Type            Varchar2(80);
   V_Excuse_Status         Varchar2(1);
   V_update_yesterday_record Boolean      := False;
   V_RecordCount           Number       := 0;
   V_LastRecord            Varchar2(5)  := 'False';
   V_Late_Start            Number;
   V_Counter_Flag          Varchar2(5)  := 'False';
   V_Plan_Flag             Varchar2(5)  := 'False';
   V_Plan_Flag1            Varchar2(5)  := 'False';
   V_DualShift_Record      Varchar2(5)  := 'False';
   V_OverTime_Deduct       Number := 0;
   V_Action_Recs           Number := 0;
   V_Distinct_Attendance_Recs Number := 0;
   V_Attendance_Recs           Number := 0;
   V_Yesterday_record_count number :=0;
   v_yesterday_record varchar2(5) :='False';
   V_PREVIOUS_DAY_GENERATED varchar2(5) :='False';

Cursor emp_list is
   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
    where a.person_type_id = b.person_type_id and
      a.person_id = c.person_id and
 --     a.employee_number != '1237' and   --to solve 14-feb-07 problem (subaiheen)
      a.business_group_id = c.business_group_id and
      b.system_person_type = 'EMP' and
      nvl(c.PRIMARY_FLAG,'Y') = 'Y' 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
      (
      (upper(replace(a.employee_number,'-','')) = upper(replace(P_EMP_NO,'-','')) and P_EMP_NO is not null)
      OR
      (a.employee_number IS NOT Null and P_EMP_NO is NULL)
      ); --if P_EMP_NO is null, then will work for all employees
BEGIN
    Begin
      select 'True' into V_PREVIOUS_DAY_GENERATED
      from HXT_ATTENDANCE_AEC
      where TRUNC(DATE_TIME_OUT)=V_PDATE-1 and rownum=1;
    Exception
      When others then V_PREVIOUS_DAY_GENERATED:='False';
    end;
    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');
       RAISE_APPLICATION_ERROR(-20101, 'ERROR: Can not generate this day attendance while previous day is not generated');
    end if;

-- 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);
 -- 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);
--V_Period_Start:=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
    HXT_TIME_ATTENDANCE_AEC.UPDATE_TEMPORARY_CARDS(V_PDate, P_Emp_No);

For i In Emp_List Loop
    FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing Records for EMP# ' ||i.EMP_NO);
      --:emp_no := i.Person_id || ' - '||i.Emp_no;
      --synchronize;
      V_Shift_Name := Null;
      V_Counter_Flag := 'False';
      V_Plan_Flag    := 'False';
      V_Plan_Flag1   := 'False';

-- This Function Will return the type of the date in which current day falls.
      -- '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);

-- To get the Employees Current Shift Timings i.e timings
      -- 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,
                        V_BBreak_In, V_BBreak_Out, V_ABreak_In, V_ABreak_Out, V_Attendanc_Type,
                        V_Shift_Name);
  If V_Shift_Name Is Not Null Then
      -- 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

/*HXT_TIME_ATTENDANCE_AEC.GET_DATE_TIME_IN_OUT(V_PDate, i.Emp_No, i.Person_Id,
               V_BBreak_In, V_ABreak_Out,
               V_Date_Time_In, V_Date_Time_Out, V_Yesterday_Record_Flag,
               V_DualShift_Record);*/
        -- 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);

Begin
         -- to check that employee come or not at all
         Select Nvl(Count(*),0) Into V_RecordCount
           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')
            And Emp_No = i.Emp_No;
      End;
    -- 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
   -- If V_DualShift_Record = 'True' And Nvl(V_RecordCount,0) = 0 Then  -- not needed by Suhaibani
   --    Null;   -- not needed by Suhaibani
   -- Else -- not needed by Suhaibani
     Declare
     -- 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;
         Cursor In_Out is
         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')
            And Emp_No = i.Emp_No
            Order By Date_Time_In;
         In_Out_Rec In_Out%RowType;
     Begin
     -- Opening the Cursor which will call different procedure and functions to calculate
     -- tardiness, absentism, overtime, standby days, hazardous high, low and counters
      Open In_Out;
      Loop
      Fetch In_Out InTo In_Out_Rec;
      If V_RecordCount = Nvl(In_Out%RowCount,0) Then
            V_LastRecord := 'True';
      Else
            V_LastRecord := 'False';
      End If;
      If Nvl(In_Out%Rowcount,0) = 0 and (In_Out%NotFound Or In_Out%NotFound is null) Then
         null;
      Else
         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_Out := In_Out_Rec.Date_Time_Out;
      End If;
      -- will get the tardiness and 1/2 day absentism of the employee

if trunc(V_DATE_TIME_OUT)=trunc(V_PDATE) then
         V_Yesterday_record:='False'; -- need in insert statments
         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_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness,
               V_Tardiness_Late, V_Attendanc_Type,
               V_Late_Start, V_OverTime_Deduct);

-- 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,
                                                    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_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);
                                                    
         -- 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,
               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); 
         else
            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,
                        V_Yesterday_BBreak_In, V_Yesterday_BBreak_Out, V_Yesterday_ABreak_In, V_Yesterday_ABreak_Out, V_Yesterday_Attendanc_Type,
                        V_Yesterday_Shift_Name);
            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_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness, V_Tardiness_Late, V_Attendanc_Type,
                            V_Late_Start, V_OverTime_Deduct);

-- 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,
                                                    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_Yesterday_Shift_Name,
                                                    V_Yesterday_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 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_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); 
               
            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.
        end if;
         If V_update_yesterday_record Then
         -- 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));
         End If;
         --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
          --dbms_output.put_line(to_char(V_DATE_TIME_IN,'DD HH24:MI'));
         BEGIN
            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) -- added creation date suhaibani
            VALUES
            (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,
            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),
            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);
         exception
           when DUP_VAL_ON_INDEX then null;
         END;

Exit When Nvl(In_Out%Rowcount,0) = 0 and (In_Out%NotFound Or In_Out%NotFound is null);
      End Loop;
      Close IN_OUT;
            if V_update_yesterday_record then
               select count(*) into v_yesterday_record_count
               from HXT_ATTENDANCE_AEC
               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
                 V_Date_Time_In  := 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,
                        V_BBreak_In, V_BBreak_Out, V_ABreak_In, V_ABreak_Out, V_Attendanc_Type,
                        V_Shift_Name);
                        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_Todays_Absent, V_Official_Work_Hours, V_Total_Work_Hours, V_Tardiness,
               V_Tardiness_Late, V_Attendanc_Type,
               V_Late_Start, V_OverTime_Deduct);
               
               -- 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,
                                                    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_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 25/7/2013 
               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

--THIS PROCEDURE WILL UPDATE THE OVERTIME TABLE WITH DETAILS
   HXT_TIME_ATTENDANCE_AEC.LOAD_OVERTIME(i.Person_Id, V_Period_Start, V_Period_End);

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 current date falls in Ramadan Month (for Muslim Emp) or A Normal Months
     -- on the basis of this date type system will select the employee's 'Work Plan' From 'OTA Submit Plan'
     -- and on the basis of Work Plan System will select the Current Shift of the employe
     -- and then from that Shift system will get SHIFT TIMINGS
     Function  GET_DATE_TYPE (P_PERSON_ID IN NUMBER,P_Date IN Date) Return Varchar2 IS
     V_Date_Type Varchar2(20) := 'Normal';
     V_Count     Number;
     V_Muslim    Varchar2(7);
     Begin
         Begin
             Select Count(*) Into V_Count
               From PAY_USER_TABLES A,
                    PAY_USER_COLUMNS B, PAY_USER_ROWS_F C, PAY_USER_COLUMN_INSTANCES_F D,
                    PAY_USER_COLUMNS E, PAY_USER_ROWS_F F, PAY_USER_COLUMN_INSTANCES_F G
              Where A.USER_TABLE_ID = B.USER_TABLE_ID
                AND A.USER_TABLE_ID = C.USER_TABLE_ID
                AND B.USER_COLUMN_ID= D.USER_COLUMN_ID
                AND C.USER_ROW_ID   = D.USER_ROW_ID
                AND A.USER_TABLE_ID = E.USER_TABLE_ID
                AND A.USER_TABLE_ID = F.USER_TABLE_ID
                AND E.USER_COLUMN_ID= G.USER_COLUMN_ID
                AND F.USER_ROW_ID   = G.USER_ROW_ID
                AND P_Date BETWEEN C.EFFECTIVE_START_DATE   AND C.EFFECTIVE_END_DATE
                AND P_Date BETWEEN D.EFFECTIVE_START_DATE   AND D.EFFECTIVE_END_DATE
                AND P_Date BETWEEN F.EFFECTIVE_START_DATE   AND F.EFFECTIVE_END_DATE
                AND P_Date BETWEEN G.EFFECTIVE_START_DATE   AND G.EFFECTIVE_END_DATE
                AND A.USER_TABLE_NAME = 'HXT Ramadan Calendar AEC'
                AND B.USER_COLUMN_NAME = E.USER_COLUMN_NAME
                AND RTRIM(LTRIM(C.ROW_LOW_RANGE_OR_NAME)) LIKE 'Start Date'
                AND RTRIM(LTRIM(F.ROW_LOW_RANGE_OR_NAME)) LIKE 'End Date'
                AND P_Date Between Trunc(To_Date(D.Value,'YYYY/MM/DD HH24:MI:SS')) And
                                Trunc(To_Date(G.Value,'YYYY/MM/DD HH24:MI:SS'));
             Exception
             When Others Then
                  V_Date_Type := 'Normal';
                  Return V_Date_Type;
         End;
         If V_Count > 0 Then
            V_Date_Type := 'Ramadan';
            -- IF NOT MUSLIN THEN V_Date_TYpe='Normal'
            BEGIN
              SELECT 'True'
              INTO V_Muslim
              FROM PER_ALL_PEOPLE_F
              WHERE PERSON_ID=P_PERSON_ID AND ATTRIBUTE5='M' AND P_DATE between EFFECTIVE_START_DATE AND NVL(EFFECTIVE_END_DATE,SYSDATE) AND ROWNUM=1;
            EXCEPTION
              WHEN OTHERS THEN V_MUSLIM:='False';
            END;

Begin
      -- get the Employee Attendancy Type i.e Standart Shift or Variable Shift etc
             Select ATTENDANCE_TYPE
               InTo P_Attendance_Type
               From PER_PERSON_ANALYSES A, HXT_OTA_INFO_AEC B
              Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
                And A.PERSON_ID = P_Person_ID
                And P_Date Between NVL(A.DATE_FROM,P_Date) AND NVL(A.DATE_TO,P_Date);
             Exception
             When No_Data_Found Then
                  Begin --By Suhaibani, if grade is 10+, then attendance type is SF (flex time)
                     Select b.name
                     INTO v_Grade
                     FROM PER_ALL_ASSIGNMENTS_F A,PER_GRADES B
                     Where a.GRADE_ID=b.GRADE_ID AND
                           A.PERSON_ID=P_Person_ID AND
                           P_Date between a.EFFECTIVE_START_DATE   and a.EFFECTIVE_END_DATE;

/*  This condition has been added temporarily by atallaha at 22-Aug-2009 to stop flexible time for the non Muslims employee in Ramadan month*/
          Select Count(*) Into V_Count
               From PAY_USER_TABLES A,
                    PAY_USER_COLUMNS B, PAY_USER_ROWS_F C, PAY_USER_COLUMN_INSTANCES_F D,
                    PAY_USER_COLUMNS E, PAY_USER_ROWS_F F, PAY_USER_COLUMN_INSTANCES_F G
              Where A.USER_TABLE_ID = B.USER_TABLE_ID
                AND A.USER_TABLE_ID = C.USER_TABLE_ID
                AND B.USER_COLUMN_ID= D.USER_COLUMN_ID
                AND C.USER_ROW_ID   = D.USER_ROW_ID
                AND A.USER_TABLE_ID = E.USER_TABLE_ID
                AND A.USER_TABLE_ID = F.USER_TABLE_ID
                AND E.USER_COLUMN_ID= G.USER_COLUMN_ID
                AND F.USER_ROW_ID   = G.USER_ROW_ID
                AND P_Date BETWEEN C.EFFECTIVE_START_DATE   AND C.EFFECTIVE_END_DATE
                AND P_Date BETWEEN D.EFFECTIVE_START_DATE   AND D.EFFECTIVE_END_DATE
                AND P_Date BETWEEN F.EFFECTIVE_START_DATE   AND F.EFFECTIVE_END_DATE
                AND P_Date BETWEEN G.EFFECTIVE_START_DATE   AND G.EFFECTIVE_END_DATE
                AND A.USER_TABLE_NAME = 'HXT Ramadan Calendar AEC'
                AND B.USER_COLUMN_NAME = E.USER_COLUMN_NAME
                AND RTRIM(LTRIM(C.ROW_LOW_RANGE_OR_NAME)) LIKE 'Start Date'
                AND RTRIM(LTRIM(F.ROW_LOW_RANGE_OR_NAME)) LIKE 'End Date'
                AND P_Date Between Trunc(To_Date(D.Value,'YYYY/MM/DD HH24:MI:SS')) And
                                Trunc(To_Date(G.Value,'YYYY/MM/DD HH24:MI:SS'));

IF to_number(v_Grade)>=10 And not V_Count =1 THEN
                        P_Attendance_Type := 'SF';
                        ELSE P_Attendance_Type := 'SS';
                     END IF;
                    Exception
                        When Others Then P_Attendance_Type := 'SS';
                  End;
             When Too_Many_Rows Then
                  P_Attendance_Type := 'SS';
             When Others Then
                  P_Attendance_Type := 'SS';
         End;
         -- In Ramadan No Flex Time
         IF V_DATE_TYPE='Ramadan' AND P_Attendance_Type='SF' THEN
             P_Attendance_Type:='SS';
         END IF;

If P_Shift_Name Is Null Then
            V_Shift_Found := True;
            Begin
      -- 1st its check emp shift entry in 'Chang Work Shift' For the current process date
                Select B.SEGMENT3 InTo P_Shift_Name
                  From PER_PERSON_ANALYSES A, PER_ANALYSIS_CRITERIA  B, FND_ID_FLEX_STRUCTURES C
                 Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
                   And A.PERSON_ID = P_Person_Id
                   And A.ID_FLEX_NUM = C.ID_FLEX_NUM
                   And C.ID_FLEX_STRUCTURE_CODE = 'CHANGE_WORK_SHIFT'
                   And P_Date Between To_Date(B.SEGMENT1,'DD-MON-YYYY') And
                                      To_Date(B.SEGMENT2,'DD-MON-YYYY')
                   And P_Date Between nvl(A.DATE_FROM,P_Date) and nvl(A.DATE_TO,P_Date) ;
                Exception
                When No_Data_Found Then
                     V_Shift_Found := False;
                When Too_Many_Rows Then
                     V_Shift_Found := False;
                When Others Then
                     V_Shift_Found := False;
            End;
            If Not V_Shift_Found Then
               V_Shift_Found := True;
               Begin
      -- If not found the Shift in 'Change Work Shift' Then it checks in 'Monthly Schedule'
                   Select TAG,'True'
                     InTo P_Shift_Name,V_Monthly_Schedule
                     From FND_LOOKUP_VALUES A, HXT_MONTHLY_SCHEDULE_AEC B
                    Where A.LOOKUP_CODE = B.SHIFT_TIME
                      And B.PERSON_ID = P_Person_ID
                      And B.DUTY_DATE = P_Date
                      And A.Lookup_Type = 'HXT_SEC_GUARD_LOCATIONS_AEC'
                      And A.Language = 'US';
                   Exception
                   When No_Data_Found Then
                        V_Shift_Found := False;
                   When Too_Many_Rows Then
                        V_Shift_Found := False;
                   When Others Then
                        V_Shift_Found := False;
               End;
            End If;
         End If;
         If Not V_Shift_Found Then
            V_Shift_Found := True;
            If P_Shift_Name Is Null Then
               Begin
              -- it shift not find in change work shift and in monthly schedule then it will get the work plan 'OTA Submit Employee'
                   Select Decode(V_Date_Type,'Normal',NORMAL_WORK_PLAN, RAMADAN_WORK_PLAN)
                     InTo V_Work_Plan
                     From PER_PERSON_ANALYSES A, HXT_OTA_INFO_AEC B
                    Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
                      And A.PERSON_ID = P_Person_ID
                      And P_Date Between NVL(A.DATE_FROM,P_Date) AND NVL(A.DATE_TO,P_Date);
                   Exception
                   When No_Data_Found Then Null;
                   When Too_Many_Rows Then NULL;
                   When Others Then null;
               End;
               Begin
               -- it will get the Current 'Work Shift' of the employee against the Work plan found in V_WORK_PLAN
               -- for the Current Processing Date
                   -- 28-Apr-2004  If Employee Record not found in any shfit type then
                   If V_Work_Plan Is Null Then
                      IF V_DATE_TYPE='Normal' THEN
                            V_Work_Plan := 'Normal Morning Shift';
                       ELSE V_Work_Plan := 'Ramadan Normal Morning Shift';
                      END IF;
                   End If;
                   ---- end ----
                   Select D.VALUE Into P_Shift_Name
                     From PAY_USER_TABLES A,
                          PAY_USER_COLUMNS B, PAY_USER_ROWS_F C, PAY_USER_COLUMN_INSTANCES_F D
                    Where A.USER_TABLE_ID = B.USER_TABLE_ID
                      AND A.USER_TABLE_ID = C.USER_TABLE_ID
                      AND B.USER_COLUMN_ID= D.USER_COLUMN_ID
                      AND C.USER_ROW_ID   = D.USER_ROW_ID
                      AND P_Date BETWEEN C.EFFECTIVE_START_DATE   AND C.EFFECTIVE_END_DATE
                      AND P_Date BETWEEN D.EFFECTIVE_START_DATE   AND D.EFFECTIVE_END_DATE
                      AND A.USER_TABLE_NAME = 'HXT Work Plan AEC'
                      AND B.USER_COLUMN_NAME = V_Work_Plan
                      AND RTRIM(LTRIM(C.ROW_LOW_RANGE_OR_NAME)) = LTRIM(RTRIM(To_Char(P_Date,'Day')));
                   Exception
                   When No_Data_Found Then Null;
                   When Too_Many_Rows Then NULL;
                   When Others Then null;
               End;
            End If;
         End If;
         -- Suhaibani 15 Nov 2006
         -- If it is "Offical" Holiday (Eid or National Day) and The employee is NOT Security Gurad (Monthly Schedule) then reset his attendance to "Off Shift"
         -- -- By A.ATALLAH 30-Jun-2013 -- change of weekend from (Thursday , Friday ) to (Friday ,Saturday ) 
         IF IS_WEEKEND_HOLIDAY(P_Date)='True' and TO_char(P_DATE,'Dy')!='Fri' and TO_char(P_DATE,'Dy')!='Sat' and V_Monthly_Schedule!='True' then
            P_Shift_Name:='Off Shift';
         end if;

Begin
      -- on the basis of P_Shift_Name System Will get the timings of that Shift
      -- and will return  before break in, befor brak out , after break in and after break out
                Select D.VALUE, G.VALUE, J.VALUE, M.VALUE
                  Into V_BBreak_In, V_BBreak_Out, V_ABreak_In, V_ABreak_Out
                  From PAY_USER_TABLES A,
                       PAY_USER_COLUMNS B, PAY_USER_ROWS_F C, PAY_USER_COLUMN_INSTANCES_F D,
                       PAY_USER_COLUMNS E, PAY_USER_ROWS_F F, PAY_USER_COLUMN_INSTANCES_F G,
                       PAY_USER_COLUMNS H, PAY_USER_ROWS_F I, PAY_USER_COLUMN_INSTANCES_F J,
                       PAY_USER_COLUMNS K, PAY_USER_ROWS_F L, PAY_USER_COLUMN_INSTANCES_F M
                 Where A.USER_TABLE_ID = B.USER_TABLE_ID
                   AND A.USER_TABLE_ID = C.USER_TABLE_ID
                   AND B.USER_COLUMN_ID= D.USER_COLUMN_ID
                   AND C.USER_ROW_ID   = D.USER_ROW_ID
                   AND A.USER_TABLE_ID = E.USER_TABLE_ID
                   AND A.USER_TABLE_ID = F.USER_TABLE_ID
                   AND E.USER_COLUMN_ID= G.USER_COLUMN_ID
                   AND F.USER_ROW_ID   = G.USER_ROW_ID
                   AND A.USER_TABLE_ID = H.USER_TABLE_ID
                   AND A.USER_TABLE_ID = I.USER_TABLE_ID
                   AND H.USER_COLUMN_ID= J.USER_COLUMN_ID
                   AND I.USER_ROW_ID   = J.USER_ROW_ID
                   AND A.USER_TABLE_ID = K.USER_TABLE_ID
                   AND A.USER_TABLE_ID = L.USER_TABLE_ID
                   AND K.USER_COLUMN_ID= M.USER_COLUMN_ID
                   AND L.USER_ROW_ID   = M.USER_ROW_ID
                   AND P_Date BETWEEN C.EFFECTIVE_START_DATE   AND C.EFFECTIVE_END_DATE
                   AND P_Date BETWEEN D.EFFECTIVE_START_DATE   AND D.EFFECTIVE_END_DATE
                   AND P_Date BETWEEN F.EFFECTIVE_START_DATE   AND F.EFFECTIVE_END_DATE
                   AND P_Date BETWEEN G.EFFECTIVE_START_DATE   AND G.EFFECTIVE_END_DATE
                   AND P_Date BETWEEN I.EFFECTIVE_START_DATE   AND I.EFFECTIVE_END_DATE
                   AND P_Date BETWEEN J.EFFECTIVE_START_DATE   AND J.EFFECTIVE_END_DATE
                   AND P_Date BETWEEN L.EFFECTIVE_START_DATE   AND L.EFFECTIVE_END_DATE
                   AND P_Date BETWEEN M.EFFECTIVE_START_DATE   AND M.EFFECTIVE_END_DATE
                   AND A.USER_TABLE_NAME = 'HXT Work Shift AEC'
                   AND B.USER_COLUMN_NAME = rtrim(ltrim(P_Shift_Name))
                   AND B.USER_COLUMN_NAME = E.USER_COLUMN_NAME
                   AND B.USER_COLUMN_NAME = H.USER_COLUMN_NAME
                   AND B.USER_COLUMN_NAME = K.USER_COLUMN_NAME
                   AND RTRIM(LTRIM(C.ROW_LOW_RANGE_OR_NAME)) = 'Before Break Time In'
                   AND RTRIM(LTRIM(F.ROW_LOW_RANGE_OR_NAME)) = 'Before Break Time Out'
                   AND RTRIM(LTRIM(I.ROW_LOW_RANGE_OR_NAME)) = 'After Break Time In'
                   AND RTRIM(LTRIM(L.ROW_LOW_RANGE_OR_NAME)) = 'After Break Time Out';
                Exception
                When No_Data_Found Then null;
                When Too_Many_Rows Then NULL;
                When Others Then null;
            End;
            Begin
      -- return the shift timing in date format
                If P_Shift_Name Is Not Null Then
                      If To_Number(SubStr(V_ABreak_Out,1,2)) < To_Number(SubStr(V_BBreak_IN,1,2)) Then
                         P_ABreak_Out := To_Date(to_char(P_Date+1,'DD-MM-YYYY')||' '||
                                         V_ABreak_Out,'DD-MM-YYYY HH24:MI');
                      Else
                         P_ABreak_Out := To_Date(to_char(P_Date,'DD-MM-YYYY')||' '||
                                         V_ABreak_Out,'DD-MM-YYYY HH24:MI');
                      End If;
                      P_BBreak_In  := To_Date(to_char(P_Date,'DD-MM-YYYY')||' '||
                                      V_BBreak_In,'DD-MM-YYYY HH24:MI');
                      P_BBreak_Out := To_Date(to_char(P_Date,'DD-MM-YYYY')||' '||
                                      V_BBreak_Out,'DD-MM-YYYY HH24:MI');
                      P_ABreak_In  := To_Date(to_char(P_Date,'DD-MM-YYYY')||' '||
                                      V_ABreak_In,'DD-MM-YYYY HH24:MI');
--                 End If;
                End If;
            End;

End GET_EMPLOYEE_SHIFT_TIME;

-- will get the tardiness and 1/2 day absentism of the employee
      -- get the global variables
      -- calculate the tardiness of the employee
      -- check the minimum ovetime restriction before and after shift
      -- make tardiness 0 for Flex and Variable Shift Type
      -- converting 'Offical Work Hours' and 'Total Work Hours' into Date Format
     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) IS
     V_LateFlag       Boolean  := False;
     V_ATotal_Hours   Number   := 0;
     V_Official_Hours Number   := 0;
     V_Hours_Minutes  Varchar2(80);
     V_MyString       Varchar2(100);
     V_Late_Start     Number;
     V_Early_End      Number;
     V_AEC_MAXIMUM_LATE_ALLOWED Number;
     V_BEFORESHIFT_OVERTIME  Number;
     V_AFTERSHIFT_OVERTIME   Number;
     V_FLEX_SHIFT_LATE_START Number;
     V_FLEX_SHIFT_EARLY_END  Number;
     Begin          --AEC_FLEX_SHIFT_LATE_START AEC_FLEX_SHIFT_EARLY_END
         Begin
             --its 45 minutes normally in AEC
             -- There is a relaxsation for flexable shift type of 60 minutes in AEC for Start Timings
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo  V_FLEX_SHIFT_LATE_START
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_FLEX_SHIFT_LATE_START'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_FLEX_SHIFT_LATE_START := 45;
         End;
         Begin
             --its 45 minutes normally in AEC
             -- There is a relaxsation for flexable shift type of 60 minutes in AEC for leaving office early
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo  V_FLEX_SHIFT_EARLY_END
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_FLEX_SHIFT_EARLY_END'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_FLEX_SHIFT_EARLY_END := 45;
         End;
         Begin
             --its 15 minutes normally in AEC
             -- There is a relaxsation of 15 minutes in AEC for Start Timings
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo V_Late_Start
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_LATE_START'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             V_Early_End := V_Late_Start;
             Exception
             When Others Then
                  V_Late_Start := 15;
                  --V_Late_Start := 15;
                  V_Early_End := 15;
         End;
         Begin
             --its 120 minutes normally in AEC
             -- maximum late an employee can be is 120 minutes i.e 2 hours
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo V_AEC_MAXIMUM_LATE_ALLOWED
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_MAXIMUM_LATE_ALLOWED'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_AEC_MAXIMUM_LATE_ALLOWED := 120;
         End;
         Begin
             -- its 30 minutes normally in AEC
             -- minimum overtime before the shift is 30 minutes in AEC
             SELECT To_Number(RTrim(Ltrim(Nvl(GLOBAL_VALUE,0)))) InTo V_BEFORESHIFT_OVERTIME
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_BEFORESHIFT_OVERTIME_ALLOWED'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                    V_BEFORESHIFT_OVERTIME:= 30;
         End;
         Begin
             -- its 30 minutes normally in AEC
             -- minimum overtime after the shift is 30 minutes in AEC
             SELECT To_Number(RTrim(Ltrim(Nvl(GLOBAL_VALUE,0)))) InTo V_AFTERSHIFT_OVERTIME
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_AFTERSHIFT_OVERTIME_ALLOWED'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                    V_AFTERSHIFT_OVERTIME := 30;
         End;
      V_ATotal_Hours    := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
      P_OverTime_Deduct := 0;
      P_Late_Start      := V_Late_Start;
      -- for flexable shift type late start and early end is assigned
      If P_Attendance_Type = 'SF' Then
           V_Late_Start :=  V_Flex_Shift_Late_Start;
           V_Early_End  :=  V_Flex_Shift_Early_end;
         P_Late_Start      := 0; --by suhaibani 12-dec-2006, No 15 Min excuse with Flextime as per policy
      else
           V_Flex_Shift_Late_Start := 0;
           V_Flex_Shift_Early_end  := 0;
      End If;
      -- if employee not come during its shift timings then Official Work Hours are 0
      If P_Date_Time_In > P_ABreak_Out Or P_Date_Time_Out < P_BBreak_In Then
         V_Official_Hours := 0;
         V_ATotal_Hours := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
      Else
      -- Here system is computing the tardiness and 1/2 day lateism
         -- Employee come after the break and go after the time
         If P_Date_Time_In >= P_ABreak_In  And P_Date_Time_Out >= P_ABreak_Out THEN
            V_Official_Hours:= TIME_SUBTRACT(P_Date_Time_In, P_ABreak_Out);
            V_ATotal_Hours  := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
               /*If TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                    P_Todays_Absent :=Nvl( P_Todays_Absent,0) + 0.5;
               ElsIf*/
            If TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In) > V_Late_Start THEN
               P_Tardiness     := TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In)-V_Flex_Shift_Late_Start;
            End If;
            P_Todays_Absent := 0.5;
         -- Employee come after the break and go before the time out
         ElsIf P_Date_Time_In >= P_ABreak_In  And P_Date_Time_Out < P_ABreak_Out THEN
            V_Official_Hours:= TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            V_ATotal_Hours  := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            P_Todays_Absent := 0.5;
            If TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In) > V_Late_Start THEN
               P_Tardiness     := TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start;
            End If;
            If TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_Early_End THEN
              -- P_Tardiness := NVL(P_Tardiness,0) + TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) - V_Flex_Shift_Early_End;
              null;        --changed by suhaibani, to not count early go
            End If;
         -- Employee come in the break and  go after the time out
         ElsIf P_Date_Time_In >= P_BBreak_Out And P_Date_Time_Out >= P_ABreak_Out THEN
            V_Official_Hours:= TIME_SUBTRACT(P_ABreak_In, P_ABreak_Out);
            V_ATotal_Hours  := TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            P_Todays_Absent := 0.5;
            P_Tardiness     := 0;
         -- Employee in  break and go after or on the break finish time
         ElsIf P_Date_Time_In >= P_BBreak_Out And P_Date_Time_Out >= P_ABreak_In  THEN
            V_Official_Hours:= TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            V_ATotal_Hours  := V_Official_Hours;
            P_Todays_Absent := 0.5;
            P_Tardiness     := 0;
            If TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_Early_End THEN
              -- P_Tardiness := NVL(P_Tardiness,0) + TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) - V_Flex_Shift_Early_End;
              null;        --changed by suhaibani, to not count early go
            End If;
         -- Employee come in  the break and  go before the break finishes
         ElsIf P_Date_Time_In >= P_BBreak_Out And P_Date_Time_Out < P_ABreak_In  THEN
            V_Official_Hours:= 0;
            V_ATotal_Hours  := 0;
            P_Todays_Absent := 1;
            P_Tardiness     := 0;
         -- Employee come before the break and go after the time out
         ElsIf P_Date_Time_In >= P_BBreak_In And P_Date_Time_Out >= P_ABreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_out);
            V_ATotal_Hours   := V_Official_Hours;
            V_Official_Hours := V_Official_Hours + TIME_SUBTRACT(P_ABreak_In, P_ABreak_out);
            V_ATotal_Hours   := V_ATotal_Hours + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_out);
               If  TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := 0.5;
                P_Tardiness     := 0;
            ElsIf TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_Late_Start THEN
                P_Todays_Absent := 0;
                P_Tardiness     := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start;
            Else
                P_Todays_Absent := 0;
                P_Tardiness     := 0;
            End If;
         -- Employee come before the break and go before the time out
         ElsIf P_Date_Time_In >= P_BBreak_In And P_Date_Time_Out >= P_ABreak_In Then
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_out);
            V_ATotal_Hours   := V_Official_Hours;
            V_Official_Hours := V_Official_Hours + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_out);
            V_ATotal_Hours   := V_ATotal_Hours + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_out);
               If  TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := 0.5;
                P_Tardiness     := 0;
            ElsIf TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_Late_Start THEN
                P_Todays_Absent := 0;
                P_Tardiness     := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start;
            End If;
               If  TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := P_Todays_Absent + 0.5;
            ElsIf TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_Early_End THEN
                -- P_Tardiness     := P_Tardiness + TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) - V_Flex_Shift_Early_End;
              null;        --changed by suhaibani, to not count early go
            End If;
         -- Employee come before the break and go in the break
         ElsIf P_Date_Time_In >= P_BBreak_In And P_Date_Time_Out >= P_BBreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_out);
            V_ATotal_Hours   := V_Official_Hours;
             If  TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := 0.5;
                P_Tardiness     := 0;
          ElsIf TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_Late_Start THEN
                P_Todays_Absent := 0;
                P_Tardiness     := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start; --due to going in break
          End If;
          P_Todays_Absent := P_Todays_Absent + 0.5; --- then absent is "1" what about official hours
         -- Employee come before the break and go before the break
         ElsIf P_Date_Time_In >= P_BBreak_In And P_Date_Time_Out < P_BBreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            V_ATotal_Hours   := V_Official_Hours;
               If  TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := 0.5;
                P_Tardiness     := 0;
            ElsIf TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_Late_Start THEN
                P_Todays_Absent := 0;
                P_Tardiness     := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start;
            Else
                P_Todays_Absent := 0;
                P_Tardiness     := 0;
            End If;
            P_Todays_Absent := P_Todays_Absent + 0.5;
         -- Empoyee come before time In and go after time Out
         ElsIf P_Date_Time_In < P_BBreak_In And P_Date_Time_Out >= P_ABreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_BBreak_In, P_BBreak_Out)+ TIME_SUBTRACT(P_ABreak_In, P_ABreak_Out);
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_Out) + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            P_Todays_Absent := 0;
            P_Tardiness     := 0;
         -- Empoyee come before time In and go before Time Out
         ElsIf P_Date_Time_In < P_BBreak_In And P_Date_Time_Out >= P_ABreak_In Then
            V_Official_Hours := TIME_SUBTRACT(P_BBreak_In, P_BBreak_Out)+ TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_Out) + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            If TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_Early_End THEN
               -- P_Tardiness := TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) - V_Flex_Shift_Early_End;
              null;        --changed by suhaibani, to not count early go
            End If;
         -- Empoyee come before time In and go in or after the Break
         ElsIf P_Date_Time_In < P_BBreak_In And P_Date_Time_Out >= P_BBreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_BBreak_In, P_BBreak_Out);
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_Out);
            P_Todays_Absent  := 0.5;
            P_Tardiness      := 0;
         -- Empoyee come before time In and go before the break
         ElsIf P_Date_Time_In < P_BBreak_In And P_Date_Time_Out < P_BBreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_Out);
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            P_Todays_Absent  := 0.5;
            P_Tardiness      := 0;
         End If;
         -- Check the minimum ovetime before the shift
         If P_Date_Time_In < P_BBreak_In Then
               If TIME_SUBTRACT(P_Date_Time_In, P_BBreak_In) < Nvl(V_BeforeShift_OVERTIME,0) Then
                    P_OverTime_Deduct := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_In);
            End If;
         End If;
         -- Check the minimum ovetime after the shift
         If P_Date_Time_Out > P_ABreak_Out Then
               If TIME_SUBTRACT(P_ABreak_Out, P_Date_Time_Out) < Nvl(V_AfterShift_OVERTIME,0) Then
                    P_OverTime_Deduct := Nvl(P_OverTime_Deduct,0) + TIME_SUBTRACT(P_ABreak_Out, P_Date_Time_Out);
               End If;
         End If;
      End If;
         -- if employee shift type is Variable and Flexable Then not Tardiness is computed
         If P_Attendance_Type IN ('SV') Then
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            If P_Date_Time_In > P_ABreak_Out Or P_Date_Time_Out < P_BBreak_In Then
               V_Official_Hours := 0;
               V_ATotal_Hours := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            End If;
            P_Todays_Absent := 0;
            P_Tardiness := 0;
            P_Tardiness_Late := 0;
         End If;
         -- converting 'Offical Work Hours' and 'Total Work Hours' into Date Format
         If V_Official_Hours >=60 then
            P_Official_Work_Hours := TO_DATE( TO_CHAR(P_Date,'DD-MM-YYYY')||' '||
                                    ( ( V_Official_Hours   - MOD(V_Official_Hours,60) )   / 60 )||':'||
                                    MOD(V_Official_Hours,60),'DD/MM/YYYY HH24:MI');
         Else
            P_Official_Work_Hours := TO_DATE( TO_CHAR(P_Date,'DD-MM-YYYY')
                                    ||' 00:'|| MOD(V_Official_Hours,60),'DD/MM/YYYY HH24:MI');
         End if;
         If V_ATotal_Hours >= 60 Then
            P_Total_Work_Hours    := TO_DATE( TO_CHAR(P_Date,'DD-MM-YYYY')||' '||
                                     ( ( V_ATotal_Hours   - MOD(V_ATotal_Hours,60) )   / 60 )||':'||
                                     MOD(V_ATotal_Hours,60),'DD-MM-YYYY HH24:MI');
         Else
            P_Total_Work_Hours    := TO_DATE( TO_CHAR(P_Date,'DD-MM-YYYY')
                                     ||' 00:'|| MOD(V_ATotal_Hours,60),'DD-MM-YYYY HH24:MI');
         End if;
     -- 18-MAY-2004 to handle the long living error of hours
     Exception when others then P_Total_Work_Hours := Trunc(P_Date); P_Official_Work_Hours := Trunc(P_Date);
     End GET_LATE_TIME;

--this function returns TRUE if P_DATE is FRI,SAT or holiday date, otherwise returns FALSE.
-- By Suhaibani 18-Jun-2006
-- By A.ATALLAH 30-Jun-2013 -- change of weekend from (Thursday , Friday ) to (Friday ,Saturday ) 
Function  IS_WEEKEND_HOLIDAY (P_Date IN Date)
 Return Varchar2 IS
V_HOLIDAY Varchar2(6);
Begin

IF TRUNC (P_Date) BETWEEN TO_DATE ('27-JUN-2013')
                            AND  TO_DATE ('29-JUN-2013')
      THEN
         IF TO_CHAR (P_DATE, 'DY') IN ('THU', 'FRI', 'SAT')
         THEN
            RETURN 'True';
         END IF;
      ELSIF TRUNC (P_Date) < TO_DATE ('27-JUN-2013')
      THEN
         IF TO_CHAR (P_DATE, 'DY') IN ('THU', 'FRI')
         THEN
            RETURN 'True';
         END IF;
      ELSE
         IF TO_CHAR (P_DATE, 'DY') IN ('FRI', 'SAT')
         THEN
            RETURN 'True';
         END IF;
    END IF;

BEGIN
     Select 'True' InTo V_Holiday
     From HXT_HOLIDAY_CALENDARS C, HXT_HOLIDAY_DAYS_VL D
     Where C.ID=D.HCL_ID
           And TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(HOLIDAY_DATE ,'DD-MON-YYYY')
           And P_Date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE and rownum=1;

Exception
     When Others Then V_HOLIDAY:='False';
   End;
   RETURN V_HOLIDAY;
END;

-- This function takes the Project Leave start and end and actual leave start and end dates
-- then return the leave taken in a given day (1 or 0.5 day).
-- By Suhaibani 7-Jun-2006
 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 IS
     V_START_DATE DATE :=trunc(NVL(P_ACTUAL_START,P_PROJECTED_START));
     V_END_DATE DATE :=trunc(NVL(P_ACTUAL_END,P_PROJECTED_END));
     V_START_TIME VARCHAR2(5):=NVL(P_ACTUAL_START_TIME,P_PROJECTED_START_TIME);
     V_END_TIME VARCHAR2(5):=NVL(P_ACTUAL_END_TIME,P_PROJECTED_END_TIME);
     Begin
         if IS_WEEKEND_HOLIDAY(P_Date)='True' THEN Return 0;-- We will need to take into account THU when worked in place of Eid holiday
         end if;

IF (V_START_TIME IS NOT NULL  AND V_END_TIME IS NOT NULL) THEN        --Added for TAMAM

if ( V_START_DATE < TRUNC(P_DATE) AND V_END_DATE > trunc(P_DATE) ) OR
            ( V_START_DATE = TRUNC(P_DATE) AND V_END_DATE > trunc(P_DATE) AND V_START_TIME='07:00') OR
            ( V_START_DATE = TRUNC(P_DATE) AND V_END_DATE = trunc(P_DATE) AND V_START_TIME='07:00' AND V_END_TIME='15:30') OR
            ( V_START_DATE < TRUNC(P_DATE) AND V_END_DATE = trunc(P_DATE) AND V_END_TIME='15:30') THEN
            Return 1 ;
         elsif ( V_END_DATE = TRUNC(P_DATE) AND V_END_TIME ='11:00' ) OR
               ( V_START_DATE = TRUNC(P_DATE) AND V_START_TIME ='11:30' ) THEN
            return 0.5;
          else Return 0;
         end if;

--Added for TAMAM, Starts

ELSE

if ( V_START_DATE < TRUNC(P_DATE) AND V_END_DATE > trunc(P_DATE) ) OR
                   ( V_START_DATE = TRUNC(P_DATE) AND V_END_DATE > trunc(P_DATE))  OR
                   ( V_START_DATE = TRUNC(P_DATE) AND V_END_DATE = trunc(P_DATE) AND P_ABS_REASON_ID IS NULL) OR
                   ( V_START_DATE < TRUNC(P_DATE) AND V_END_DATE = trunc(P_DATE)) THEN
                   Return 1 ;
                elsif ( V_END_DATE = TRUNC(P_DATE) AND  V_START_DATE = TRUNC(P_DATE) AND P_ABS_REASON_ID in (11,101,102,103,104,105,106,107,108,109,113,115,117,119,120,116) ) THEN
                   return 0.5;
                 else Return 0;
         end if;

END IF;

--Added for TAMAM, Ends

End GET_LEAVE_IN_DAY;

-- will maintint the counters 15 minute late etc, leave , absentism etc.
      -- get the data of leave recrods
      -- calculate the absentism
      -- calculate the 15 , 30 and 60 minute counters
      -- compare actual and leave recrods
     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) IS
     V_Counter_15_Minutes    Number;
     V_Counter_30_Minutes    Number;
     V_Counter_60_Minutes    Number;
     V_Cumulative_Late_Hours Number;
     V_Absent_Days           Number:=0;
     V_Leave_Days            Number:=0;
     V_Date_PS               Date;
     V_Date_PE               Date;
     V_Date_Start            Date;
     V_Date_End              Date;
      Cursor ALL_LEAVE_IN_DAY is   -- Cursor to get leave_type and count in a given day (Suhaibani)
           SELECT B.NAME LEAVE_TYPE,sum(GET_LEAVE_IN_DAY(trunc(P_DATE),A.DATE_PROJECTED_START,A.DATE_PROJECTED_END,A.DATE_START, A.DATE_END,
                                    A.TIME_PROJECTED_START,A.TIME_PROJECTED_END,A.TIME_START,A.TIME_END,A.ABS_ATTENDANCE_REASON_ID)) Leave_Sum
           From PER_ABSENCE_ATTENDANCES A, PER_ABSENCE_ATTENDANCE_TYPES B
           Where A.ABSENCE_ATTENDANCE_TYPE_ID = B.ABSENCE_ATTENDANCE_TYPE_ID AND
                      A.PERSON_ID = P_Person_Id AND
                      trunc(P_Date) Between trunc(NVL(A.DATE_START,A.DATE_PROJECTED_START)) And trunc(NVL(A.DATE_END,A.DATE_PROJECTED_END))
          GROUP BY B.NAME;
     Begin
         P_Counter_15_Minutes:= 0;
         P_Counter_30_Minutes:= 0;
         P_Counter_60_Minutes:= 0;
         P_Cumulative_Late_Hours := 0;
         P_Total_Absent_Days := 0;
         P_Counter_Leave_Days:= 0;
         P_Leave_Type := NULL;

If P_Counter_Flag = 'False' Then
            If P_LastRecrod='True' Or To_Char(P_Official_Work_Hours,'HH24:MI') Not Like '00:00' Then
                  P_Counter_Flag := 'True';
                  V_Absent_Days := Nvl(P_Total_Absent_Days,0);
                  If P_Shift_Name Like 'Off Shift' And To_Char(P_Official_Work_Hours,'HH24:MI') LIKE '00:00' Then
                     -- in off shif no abentism
                     P_Total_Absent_Days := 0;
                  ElsIf To_Char(P_Official_Work_Hours,'HH24:MI') LIKE '00:00' Then
                     -- Employee is absent
                     P_Total_Absent_Days := 1;
                  Else
                     -- Employee is late then update the counters
                     If P_Tardiness > To_Number(P_Late_Start) Then
                        If NVL(P_Tardiness,0) > 60 Then
                           P_Counter_60_Minutes :=  1;
                        Elsif P_Tardiness > 30 then
                           P_Counter_30_Minutes :=  1;
                        Elsif P_Tardiness > 15 then          -- changed by suhaibani 12-dec-06
                           P_Counter_15_Minutes :=  1;
                        Elsif P_Tardiness > To_Number(P_Late_Start) then
                           null;
                           --by suhaibani 12-dec-06, we may need to add new counter for Tardiness <15 min for Flextime employee. (for Flex time, no 15 min late exuse as per policy)
                        End if;
                        P_Cumulative_Late_Hours  := Nvl(P_Tardiness,0);
                        P_Cumulative_Late_Hours  := ( ( P_Cumulative_Late_Hours - MOD(P_Cumulative_Late_Hours,60) ) / 60||
                                                     '.'||LPad(MOD(P_Cumulative_Late_Hours,60),2,0) ) ;
                        P_Excuse_Status := 'L';  -- Excuse status will be 'Late'
                     End If;
                     If P_Date_Time_In Is Null Then
                        P_Total_Absent_Days := 1;
                     Else
                        P_Total_Absent_Days := P_Todays_Absent;
                     End If;
                  End If;

V_Leave_Days:=null;
               P_Leave_type:=null;
               if P_LastRecrod='True' then  -- Calaculate leave only in last record (Need to recheck)- suhaibani
                  FOR L in ALL_LEAVE_IN_DAY  LOOP     -- in case there are more than one leave, then combine them (Suhaibani)
                     if nvl(L.Leave_Sum,0)!=0 then
                        V_Leave_Days:=nvl(V_Leave_Days,0)+ L.Leave_Sum;
                        if P_Leave_Type!=null then P_Leave_type:=P_Leave_Type||' + ' ||L.Leave_type||'('||to_char(nvl(V_Leave_Days,0))||')';
                        else P_Leave_type:=L.Leave_type||'('||to_char(nvl(V_Leave_Days,0))||')';
                        end if;
                     end if;
                  end loop;
               end if;
            Else
               P_Leave_Type := Null;
            End If;
         Else
            P_Leave_Type := Null;
         End If;
     End GET_COUNTER_INFO;
     -- This Procedure Calculate the OverTime, Standby, Hazardous Low and High of the employee
     -- Selet the employee Submit Plans Details
     -- get the Overtime, standby , Hazadious information from submit plan
     -- calclute the actual Ovet time
     -- convert ovetime into time format
     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) IS 
     V_Holiday_Date         Date;
     V_OT_Days              Number;
     V_OT_Fraction          Number;
     V_Travel_Hours         Number;
     V_Weekly_Work_Hours    Number;
     V_Weekly_OTR           Number;
     Weekly_leaves          Number; --by hadba, sum of leaves in the week
     
     Cursor Plan_Detail is
     -- Selet the employee Submit Plans Details
     Select B.SEGMENT1 Plan_Type, To_Date(SEGMENT2,'DD-MON-YYYY') Period_From ,
            To_Date(SEGMENT3,'DD-MON-YYYY') Period_To, SEGMENT4 Planned_Hours, SEGMENT5 Planned_days
       From PER_PERSON_ANALYSES A, PER_ANALYSIS_CRITERIA  B, FND_ID_FLEX_STRUCTURES C
      Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
        And A.PERSON_ID = P_Person_Id
        And A.ID_FLEX_NUM = C.ID_FLEX_NUM
        And C.ID_FLEX_STRUCTURE_CODE = 'SUBMIT_PLAN'
        And P_Date Between To_Date(B.SEGMENT2,'DD-MON-YYYY') And Nvl(To_Date(B.SEGMENT3,'DD-MON-YYYY'),P_Date)
        AND P_Date Between A.DATE_FROM And nvl(A.DATE_TO,P_Date);
     Begin
        -- Select the date as if the current processing date falls in Holiday Calendar

Begin
             Select HOLIDAY_DATE InTo V_Holiday_Date
               From HXT_HOLIDAY_CALENDARS C, HXT_HOLIDAY_DAYS_VL D
              Where C.ID=D.HCL_ID
                And TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(HOLIDAY_DATE ,'DD-MON-YYYY')
                And P_Date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
             Exception
                 When Others Then V_Holiday_Date:=SYSDATE+10000;   --set virtual invalid holiday date By suhaibani 9-1-2007
         End;
         Begin
             --its 60 minutes normally in AEC
             -- in holidays 60 minutes over time is given
             SELECT To_Number(RTrim(Ltrim(Nvl(GLOBAL_VALUE,0)))) InTo V_Travel_Hours
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_TRAVEL_HOURS'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                    V_Travel_Hours := 60;
         End;
         -- Calculating the Overtime, standby , Hazadious information from submit plan
         If P_Plan_Flag = 'False' Then
            If P_LastRecord='True' Or To_Char(P_Official_Work_Hours,'HH24:MI') Not Like '00:00' Then
               P_Plan_Flag := 'True';
               FOR plan_detail_rec in plan_detail loop
                   IF plan_detail_rec.plan_type = 'OT' THEN
                      IF NVL(Plan_Detail_Rec.PLANNED_HOURS,0) > 0 THEN
                         -- find the OT_Days by finding the diff between two dates
                         IF NVL(Plan_Detail_Rec.PLANNED_DAYS,0) > 0 THEN
                            V_OT_Days := Plan_Detail_Rec.PLANNED_DAYS;
                         ELSE
                            V_OT_Days := Plan_Detail_Rec.PERIOD_TO - plan_detail_rec.PERIOD_FROM + 1;
                         END IF;
                         V_OT_Fraction := MOD( NVL(Plan_Detail_Rec.PLANNED_HOURS,0),V_OT_Days );
                         IF P_Date = Plan_Detail_Rec.PERIOD_FROM THEN
                            P_OTR := ( (NVL(plan_detail_rec.PLANNED_HOURS,0) - V_OT_Fraction ) / V_OT_Days ) +
                                     V_OT_Fraction;
                         ELSE
                            P_OTR := ( (NVL(plan_detail_rec.PLANNED_HOURS,0) - V_OT_Fraction ) / V_OT_Days );
                         END IF;
                         -- if day is Friday then the over time is Special Over Time
                         IF TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY')
                            OR TO_CHAR(P_Date,'DAY')  IN ('FRIDAY') then
                            P_OTS := P_OTR + 1;
                            P_OTR := Null;
                            P_OTR_Approved := Null;
                         END IF;
                      END IF;
                   -- find Hazard Low
                   ElsIf Plan_Detail_Rec.PLAN_TYPE = 'HZ'  THEN
                      P_Hazard_Days := 1;
                   -- find Hazard High
                   ElsIf Plan_Detail_Rec.PLAN_TYPE = 'HZH'  THEN
                      P_Hazard_Days_Hight := 1;
                   -- find Standby
                   ElsIf Plan_Detail_Rec.PLAN_TYPE = 'SB' THEN
                      P_Stand_By_Days := 1;
                   End If;
               End Loop;
            End if;
         End If;
         -- For Flex Shift Type Overtime is Calculated at Weekend i.e THURSDAY only for thr whole week
         -- -- By A.ATALLAH 30-Jun-2013 -- change of weekend from (Thursday , Friday ) to (Friday ,Saturday ) 
         
         If P_Attendance_Type = 'SF' AND LTRIM(RTRIM(TO_CHAR(P_Date,'DAY'))) Not IN ('FRIDAY','SATURDAY') AND TO_CHAR(P_Date,'DD-MON-YYYY')!=TO_CHAR(V_Holiday_Date,'DD-MON-YYYY') Then
            If LTRIM(RTRIM(TO_CHAR(P_Date,'DAY'))) = 'THURSDAY' And P_Plan_Flag1 = 'False' Then
               If P_LastRecord='True' Or To_Char(P_Official_Work_Hours,'HH24:MI') Not Like '00:00' Then
                  P_Plan_Flag1 := 'True';
                  Begin
                     -- 20 Feb 2007 Query Updated Suhaibani added NVL(0) to solve new employees problem starting on Wed.
                      Select NVL(Sum(apps.HXT_TIME_ATTENDANCE_AEC.CONVERT_TIMETO_NUMBER(To_Number(To_Char(Total_Work_Hours,'HH24.MI')))),0),
                             NVL(Sum(apps.HXT_TIME_ATTENDANCE_AEC.CONVERT_TIMETO_NUMBER(Nvl(REGULAR_OVERTIME_SUGGESTED,0))),0), sum(nvl(COUNTER_LEAVE_DAYS,0)) --by hadba leave day
                        InTo V_Weekly_Work_Hours, V_Weekly_OTR, Weekly_leaves -- by hadba weekly leave 
                        From HXT_ATTENDANCE_AEC
                       Where Person_Id = P_Person_Id
                         And Trunc(Date_Time_out) Between P_Date-4 And P_Date-1; -- changed by suhaibani it was -6 to -1
                                                -- 18May09 changed by suhaibani to Date_Time_out to correct weekly hrs calculations for overtime, it was Date_Time_in
                  End;
                  V_Weekly_Work_Hours := V_Weekly_Work_Hours + CONVERT_TIMETO_NUMBER(To_Number(To_Char(P_Total_Work_Hours,'HH24.MI')));
                  P_OTR := V_Weekly_OTR + CONVERT_TIMETO_NUMBER(Nvl(P_OTR,0));
                  If P_Date_Type = 'Normal' Then --by hadba to add excused hours full day=1 , half day=0.5 into work hours         
                    if P_Leave_Type like '%5%' then-- to add Thu half day leave         
                    Weekly_leaves := Weekly_leaves + 0.5;         
                    end if;         
                    if P_Leave_Type like '%1%' then-- to add Thu full day leave         
                    Weekly_leaves := Weekly_leaves + 1;         
                    end if;         
                    V_Weekly_Work_Hours := V_Weekly_Work_Hours+((Weekly_leaves)*8);-- by hadba to add the rest week's leaves from Sat to Tue         
                    V_Weekly_Work_Hours := Convert_NumberTo_Time(V_Weekly_Work_Hours);         
                    else
                    V_Weekly_Work_Hours := Convert_NumberTo_Time(V_Weekly_Work_Hours);
                  end if;
                  P_OTR := Convert_NumberTo_Time(P_OTR);
                  
                  P_OTR_Approved := 0;
                  If P_Date_Type = 'Normal' Then
                     If V_Weekly_Work_Hours > 40 Then
                        P_OTR_Approved := V_Weekly_Work_Hours - 40;
                     End If;
                  Else
                     If V_Weekly_Work_Hours > 30 Then
                        P_OTR_Approved := V_Weekly_Work_Hours - 30;
                     End If;
                  End If;
               End If;
            End If;
         Else
            -- Calculating the Actual Overtime of the employee
            If P_Total_Work_Hours > P_Official_Work_Hours Then  --And To_Char(P_Official_Work_Hours,'HH24:Mi') Like '00:00'
               If TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY')
                  OR LTRIM(RTRIM(TO_CHAR(P_Date,'DAY')))  IN ('FRIDAY') then
                     --P_OTS_Approved := Nvl(Time_Subtract(P_Official_Work_Hours, P_Total_Work_Hours),0)+60-P_OverTime_Deduct;  changed by suhaibani no need for +60
                     P_OTS_Approved := Nvl(Time_Subtract(P_Official_Work_Hours, P_Total_Work_Hours),0)-P_OverTime_Deduct;
                     P_OTS_Approved := To_Number( ( P_OTS_Approved - MOD(P_OTS_Approved,60) ) / 60||'.'||LPad(MOD(P_OTS_Approved,60),2,0));
               Else
                     P_OTR_Approved := NVL(Time_Subtract(P_Official_Work_Hours, P_Total_Work_Hours),0)-P_OverTime_Deduct;
                     P_OTR_Approved := To_Number( ( P_OTR_Approved - MOD(P_OTR_Approved,60) ) / 60||'.'||LPad(MOD(P_OTR_Approved,60),2,0));
               End If;
            End If;
         End If;
         -- 30 minutes are deduted from the overtim if it is grater than 5 hours. -- Added hloday condition by suhaibani 9-1-2007, added  THU,FRI Check 17-1-2007
         -- By A.ATALLAH 30-Jun-2013 -- change of weekend from (Thursday , Friday ) to (Friday ,Saturday ) 
         If P_OTR_Approved > 5 and (P_Attendance_Type NOT LIKE 'SF' OR  TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY') OR LTRIM(RTRIM(TO_CHAR(P_Date,'DAY')))  IN ('FRIDAY','SATURDAY')) Then
               P_OTR_Approved := Convert_NumberTo_Time(Convert_TimeTo_Number(P_OTR_Approved) - (30/60));
         End If;                                               -- Added hloday condition by suhaibani 9-1-2007, added FRI,SAT Check 17-1-2007
         If P_OTS_Approved > 5 and (P_Attendance_Type NOT LIKE 'SF' OR TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY') OR LTRIM(RTRIM(TO_CHAR(P_Date,'DAY')))  IN ('FRIDAY','SATURDAY')) Then
               P_OTS_Approved := Convert_NumberTo_Time(Convert_TimeTo_Number(P_OTS_Approved) - (30/60));
         End If;
         -- this block was moved to be after overtime 30 minuts deduction. it was before it 17-1-2007
         If TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY') --- Travel 60 min given to emp
            OR LTRIM(RTRIM(TO_CHAR(P_Date,'DAY')))  IN ('FRIDAY','SATURDAY') then
               If P_LastRecord='True' then
                  If P_OTR_Approved > 0 Then
                    P_OTR_Approved := Convert_NumberTo_Time(Convert_TimeTo_Number(P_OTR_Approved) + V_Travel_Hours/60);
                  ElsIf P_OTS_Approved > 0 Then
                    P_OTS_Approved := Convert_NumberTo_Time(Convert_TimeTo_Number(P_OTS_Approved) + V_Travel_Hours/60);
                  End If;
             End If;
         End if;
     End GET_PLAN_DETAILS;
     -- it Procedure will get the start and end date for the period in which P_Date will Fall
     Procedure TIME_ATTENDANCE_PERIOD (P_Date IN Date, P_Start_Date Out DATE, P_End_Date Out Date) IS
     Begin
         Select To_Date(Substr(D.VALUE,1,10),'YYYY/MM/DD'), to_date(substr(G.VALUE,1,10),'YYYY/MM/DD')
           Into P_Start_Date, P_End_Date
           From PAY_USER_TABLES A,
                PAY_USER_COLUMNS B, PAY_USER_ROWS_F C, PAY_USER_COLUMN_INSTANCES_F D,
                PAY_USER_COLUMNS E, PAY_USER_ROWS_F F, PAY_USER_COLUMN_INSTANCES_F G
          WHERE A.USER_TABLE_ID = B.USER_TABLE_ID
            AND A.USER_TABLE_ID = C.USER_TABLE_ID
            AND B.USER_COLUMN_ID= D.USER_COLUMN_ID
            AND C.USER_ROW_ID   = D.USER_ROW_ID
            AND A.USER_TABLE_ID = E.USER_TABLE_ID
            AND A.USER_TABLE_ID = F.USER_TABLE_ID
            AND E.USER_COLUMN_ID= G.USER_COLUMN_ID
            AND F.USER_ROW_ID   = G.USER_ROW_ID
            AND SYSDATE BETWEEN C.EFFECTIVE_START_DATE   AND C.EFFECTIVE_END_DATE
            AND SYSDATE BETWEEN D.EFFECTIVE_START_DATE   AND D.EFFECTIVE_END_DATE
            AND SYSDATE BETWEEN F.EFFECTIVE_START_DATE   AND F.EFFECTIVE_END_DATE
            AND SYSDATE BETWEEN G.EFFECTIVE_START_DATE   AND G.EFFECTIVE_END_DATE
            AND A.USER_TABLE_NAME = 'HXT Time Attendance Calendar AEC'
            AND B.USER_COLUMN_NAME = E.USER_COLUMN_NAME
            AND RTRIM(LTRIM(C.ROW_LOW_RANGE_OR_NAME)) LIKE 'Start Date'
            AND RTRIM(LTRIM(F.ROW_LOW_RANGE_OR_NAME)) LIKE 'End Date'
            --AND P_Date BETWEEN To_Date(Substr(D.VALUE,1,10),'YYYY/MM/DD') AND To_Date(Substr(G.VALUE,1,10),'YYYY/MM/DD') --by suhaibani bug
            AND to_char(P_date,'YYYY/MM/DD') BETWEEN Substr(D.VALUE,1,10) AND Substr(G.VALUE,1,10)
            and 'S' = APPS.HXT_TIME_ATTENDANCE_AEC.RET_DATE(D.VALUE)
            and 'S' = APPS.HXT_TIME_ATTENDANCE_AEC.RET_DATE(G.VALUE);
          Exception
          When No_Data_Found Then
               P_Start_Date := Null;
               P_End_Date   := Null;
          When Others Then
               P_Start_Date := Null;
               P_End_Date   := Null;
     End TIME_ATTENDANCE_PERIOD;
     -- Time Subtract Function take two inputs as date and return the diff between these date in Minutes Form
     Function TIME_SUBTRACT (P_time1 IN Date, P_time2 IN Date)  Return Number Is
     hr1 number  := TO_CHAR(P_time1,'HH24');
     hr2 number  := TO_CHAR(P_time2,'HH24');
     thr number;
     min1 number := TO_CHAR(P_time1,'MI');
     min2 number := TO_CHAR(P_time2,'MI');
     tmin number;
     Begin
        -- p_time2 is next date or further more etc
        If trunc(P_Time2)-trunc(P_Time1) >= 1 Then
             hr2 := hr2 + 24* ( trunc(P_Time2)-trunc(P_Time1));
        End if;
        If hr1 > hr2 then
           hr2 := hr2 + 24;
        end if;
        if min1 > min2 then
           min2 := min2 + 60;
           hr2 := hr2 - 1;
        end if;
        thr  := hr2  - hr1;
        tmin := min2 - min1;
        return abs( (thr*60)+tmin );
     End TIME_SUBTRACT;
  -- this will update the record if the plan is enterd after the process run for the current day
  -- i.e if the plan is enterd after the date is passed then this will update the employee record
  -- but these plans should fall in current attendance period.
  -- the record which will be update if these are already singed then they will be marded unsigned
  -- by the system and the manager have to update these unsigned records again.
     PROCEDURE SUBMIT_PLAN_CHECK(P_Person_Id In Number, P_Period_Start In Date, P_Period_End In Date) IS
             V_Date_Type Varchar2(20);
             V_Attendance_Type Varchar2(80);
             V_OTR Number;
             V_OTS Number;
             V_OTR_Approved Number;
             V_OTS_Approved Number;
             V_Hazard_Days Number;
             V_HAZARD_DAYS_HIGH Number;
             V_Stand_By_Days Number;
             V_Plan_Flag Varchar2(5) :=  'False';
             V_Plan_Flag1 Varchar2(5) := 'False';
             V_LastRecord Varchar2(5) := 'False';
             V_last_record date;
             V_Temp_Date date := null;
             V_OverTime_Deduct Number := 0;
             V_Leave_Type  Varchar2(80);
     -- selecting those arecords which have no ovetime etc from employee attendance table
     Cursor Late_Submit_Plan is
     Select * From HXT_ATTENDANCE_AEC D
      Where Person_Id = P_Person_Id
        And Trunc(d.Date_Time_Out) Between P_Period_Start and P_Period_End
        And (Nvl(D.REGULAR_OVERTIME_SUGGESTED,0)= 0 Or Nvl(D.SPECIAL_OVERTIME_SUGGESTED,0) = 0 Or
             Nvl(D.HAZARD_DAYS,0) = 0 Or Nvl(D.HAZARD_DAYS_HIGH,0) = 0 Or Nvl(D.STAND_BY_DAYS,0) = 0)
        And d.Person_Id In(
            Select a.Person_Id
              From PER_PERSON_ANALYSES A, PER_ANALYSIS_CRITERIA  B, FND_ID_FLEX_STRUCTURES C
             Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
               And A.PERSON_ID = d.Person_Id
               AND C.ID_FLEX_NUM=50326 --Added this line to use index and overcome SQL type mismatch ERROR in this query, Suhaibani 2-Nov-2006
               And A.ID_FLEX_NUM = C.ID_FLEX_NUM
               And C.ID_FLEX_STRUCTURE_CODE = 'SUBMIT_PLAN'
               And Trunc(d.Date_Time_Out) Between To_Date(B.SEGMENT2,'DD-MON-YYYY') And Nvl(To_Date(B.SEGMENT3,'DD-MON-YYYY'),Trunc(d.Date_Time_Out))
               AND Trunc(d.Date_Time_Out) Between A.DATE_FROM And nvl(A.DATE_TO,Trunc(d.Date_Time_Out)) )
      Order by d.date_time_Out;

Begin
         For i In Late_Submit_Plan Loop
            V_Date_Type := HXT_TIME_ATTENDANCE_AEC.GET_DATE_TYPE (P_PERSON_ID,Trunc(i.Date_Time_Out));
            Begin
                -- get the attendance type of the employee because it might also be changed
                Select ATTENDANCE_TYPE InTo V_Attendance_Type From PER_PERSON_ANALYSES A, HXT_OTA_INFO_AEC B
                 Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID And A.PERSON_ID = P_Person_ID
                   And Trunc(i.Date_Time_Out) Between NVL(A.DATE_FROM,Trunc(i.Date_Time_Out)) AND NVL(A.DATE_TO,Trunc(i.Date_Time_Out));
                Exception When Others Then  V_Attendance_Type := 'SS';
            End;
            Begin
                -- if employee has more than one record in one day then only last record will be updated
                Select max(date_time_Out) Into V_last_record
                  From HXT_ATTENDANCE_AEC a
                 Where a.person_id = i.person_id
                  and  trunc(date_time_out) = trunc(i.date_time_Out);
            End;
            If V_last_record = i.date_time_out Then
               V_LastRecord := 'True';
            Else
               V_LastRecord := 'False';
            End If;
            If V_Temp_Date = Trunc(i.Date_Time_Out) Then
               V_Temp_Date  := Trunc(i.Date_Time_Out);
            else
               V_Temp_Date  := Trunc(i.Date_Time_Out);
               V_Plan_Flag  := 'False';
               V_Plan_Flag1 := 'False';
            End If;
            -- calculate the ovetime , standby days and hazardous low and high
            HXT_TIME_ATTENDANCE_AEC.GET_PLAN_DETAILS (Trunc(i.Date_Time_Out), i.Person_ID, V_Date_Type,
                                                           i.Official_Work_Hours, i.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_Attendance_Type,
                                                           V_LastRecord, V_Plan_Flag, V_Plan_Flag1, V_OverTime_Deduct, V_Leave_Type); 
            -- update the table with new paln information
            Update HXT_ATTENDANCE_AEC Set REGULAR_OVERTIME_SUGGESTED = V_OTR, SPECIAL_OVERTIME_SUGGESTED = V_OTS,
                                      HAZARD_DAYS = V_HAZARD_DAYS, HAZARD_DAYS_HIGH = V_HAZARD_DAYS_HIGH,
                                      STAND_BY_DAYS = V_Stand_By_Days, LAST_UPDATED_BY = 3, LAST_UPDATE_DATE = SYSDATE   -- added last updated by suhaibani
            Where Person_Id = i.Person_Id And date_time_out = i.date_Time_out;
            -- unsigne the all records of the employee of that perticulare date
            Update HXT_ATTENDANCE_AEC Set SIGGNED='N' , LAST_UPDATED_BY = 3, LAST_UPDATE_DATE = SYSDATE   -- added last updated by suhaibani
            Where trunc(date_time_out) = Trunc(i.date_Time_out)
                  And Nvl(Siggned,'N') = 'Y' And Organization_Id = i.Organization_Id;
         End Loop i;
     End;

-- This Proceudre update the leave and absentism of the employee table basis on the inoformation found
     -- in the back dated leave record in Leave system.
     -- unsigne the all records of the employee of that perticulare date which are updated
PROCEDURE LEAVE_DIFFERENCE(P_Person_Id In Number, P_Period_Start In Date, P_Period_End In Date) IS
     Start_Date Date := P_Period_Start-40; --By atallah added 40 days for backdated vacation case (late entry and approval)
     End_Date  Date :=  P_Period_End;
     -- select the leave record of the employee from the leave system
     Cursor Leaves Is
             Select a.Person_id, B.NAME, A.DATE_PROJECTED_START, A.DATE_PROJECTED_END, A.DATE_START, A.DATE_END,A.TIME_PROJECTED_START, A.TIME_PROJECTED_END, A.TIME_START, A.TIME_END
             From PER_ABSENCE_ATTENDANCES A, PER_ABSENCE_ATTENDANCE_TYPES B
             Where A.ABSENCE_ATTENDANCE_TYPE_ID = B.ABSENCE_ATTENDANCE_TYPE_ID
             And   A.Person_Id=P_Person_Id
             And (   A.DATE_PROJECTED_START    Between Nvl(Start_Date,A.DATE_PROJECTED_START) And Nvl( End_Date,A.DATE_PROJECTED_END) OR
                     A.DATE_PROJECTED_END      Between Nvl(Start_Date,A.DATE_PROJECTED_START) And Nvl( End_Date,A.DATE_PROJECTED_END) OR
                     A.DATE_START              Between Nvl(Start_Date,A.DATE_START) And Nvl( End_Date,A.DATE_END) OR
                     A.DATE_END                Between Nvl(Start_Date,A.DATE_START) And Nvl( End_Date,A.DATE_END) );

Cursor ALL_LEAVE_IN_DAY(P_DATE DATE) is   -- Cursor to get leave_type and count in a given day (Suhaibani)
           SELECT B.NAME LEAVE_TYPE,sum(GET_LEAVE_IN_DAY(trunc(P_DATE),A.DATE_PROJECTED_START,A.DATE_PROJECTED_END,A.DATE_START, A.DATE_END,
                                    A.TIME_PROJECTED_START,A.TIME_PROJECTED_END,A.TIME_START,A.TIME_END,A.ABS_ATTENDANCE_REASON_ID)) Leave_Sum
           From PER_ABSENCE_ATTENDANCES A, PER_ABSENCE_ATTENDANCE_TYPES B
           Where A.ABSENCE_ATTENDANCE_TYPE_ID = B.ABSENCE_ATTENDANCE_TYPE_ID AND
                      A.PERSON_ID = P_Person_Id AND
                      trunc(P_Date) Between trunc(NVL(A.DATE_START,A.DATE_PROJECTED_START)) And trunc(NVL(A.DATE_END,A.DATE_PROJECTED_END))
          GROUP BY B.NAME;
    BEGIN
       --This loop will for the reocrds find in leave system
         For i in Leaves Loop
            Declare
            V_Leave_Days Number := 0;
            V_Leave_Type Varchar2(70);
             -- select those record from attendance system whose abesentism is found
             -- if leave record found against these absend recrod then this absentism will be converted into leave
             Cursor Attendance Is
                  Select *       -- Need to select only one record per day, need to re-write it again suhaibani
                    From HXT_ATTENDANCE_AEC A
                   Where Person_Id = i.Person_id AND
                            ( Trunc(Date_Time_OUT) Between i.DATE_PROJECTED_START And i.DATE_PROJECTED_END OR
                              Trunc(Date_Time_OUT) Between NVL(i.DATE_START,i.DATE_PROJECTED_START) And NVL(i.DATE_END,i.DATE_PROJECTED_END) ) and
                             Date_Time_OUT=(select max(Date_Time_OUT) from HXT_ATTENDANCE_AEC B where B.Person_Id = i.Person_id and trunc(B.Date_Time_OUT)=trunc(A.Date_Time_OUT));
            Begin
               For j In Attendance Loop
                 begin
                   V_Leave_Days := Null;
                   V_Leave_Type := Null;
                   for L in ALL_LEAVE_IN_DAY(j.DATE_TIME_OUT) LOOP  -- in case there are more than one leave, then combine them (Suhaibani)
                       if nvl(L.Leave_Sum,0)!=0 then
                          V_Leave_Days:=nvl(V_Leave_Days,0)+ L.Leave_Sum;
                          if V_Leave_Type!=null then V_Leave_type:=V_Leave_Type||' + ' ||L.Leave_type||'('||to_char(nvl(V_Leave_Days,0))||')';
                             else V_Leave_type:=L.Leave_type||'('||to_char(nvl(V_Leave_Days,0))||')';
                          end if;
                       end if;
                   end loop;
                 End;

if NVL(V_Leave_Days,0)!= nvl(j.Counter_Leave_Days,0) OR NVL(V_Leave_Type,'X')!=NVL(j.STATUS,'X') then --by suhaibani, check leave type mismatch also.
                   -- udpating the absent recrod into leave records
                    Update HXT_ATTENDANCE_AEC Set Counter_Leave_Days = V_Leave_Days, Counter_Absent_Days = 0, STATUS=V_Leave_Type, LAST_UPDATED_BY = 3, LAST_UPDATE_DATE = SYSDATE   -- added last updated by suhaibani
                    Where Person_id = i.Person_Id
                    And Date_Time_OUT = j.Date_Time_Out;

-- unsigne the all records of the employee of that perticulare date
                   Update HXT_ATTENDANCE_AEC Set SIGGNED='N' , LAST_UPDATED_BY = 3, LAST_UPDATE_DATE = SYSDATE   -- added last updated by suhaibani
                   Where trunc(date_time_out) = Trunc(j.date_Time_out)
                   And Nvl(Siggned,'N') = 'Y' And Organization_Id = j.Organization_Id And Person_Id=j.Person_Id; -- Added by suhaibani 16-oct-2006
                end if;
           End Loop;
        End;
     End loop;
     Declare
     -- this is ther reversal of the above loop i.e. Check that if in attendance system leave is found
     -- but in leave system no record related to that leave found then convert that attendance record
     -- into absent record
     Cursor Reversal is
     Select *
       From HXT_ATTENDANCE_AEC a
      Where Trunc(a.Date_Time_Out) Between Start_Date And End_Date
        And a.person_id=P_PERSON_ID -- added this line, 3-dec-2006
        And a.Counter_Leave_Days > 0
        And NOT EXISTS
            (Select *
               From PER_ABSENCE_ATTENDANCES b
              Where a.Person_Id = b.Person_Id
                And (   Trunc(a.Date_Time_Out) Between Date_Projected_Start And Nvl(Date_Projected_End,Trunc(a.Date_Time_Out))
                     Or Trunc(a.Date_Time_Out) Between Date_Start           And Nvl(Date_End          ,Trunc(a.Date_Time_Out))
                    )
            );
       Begin
            For k in Reversal Loop
            -- converting the leave record into absent record
            Update HXT_ATTENDANCE_AEC Set Counter_Absent_Days =  k.Counter_Leave_Days, Counter_Leave_Days = 0, Status = null, LAST_UPDATED_BY = 3, LAST_UPDATE_DATE = SYSDATE   -- added last updated by suhaibani
             Where Person_Id = k.Person_Id
               And Date_Time_Out = k.Date_Time_Out;
            Update HXT_ATTENDANCE_AEC Set SIGGNED='N' Where trunc(date_time_out) = Trunc(k.date_Time_out)
                                                     And Nvl(Siggned,'N') = 'Y' And Organization_Id = k.Organization_Id And Person_Id = k.Person_Id;
            End Loop;
       End;
 END LEAVE_DIFFERENCE;

-- 
         For j in 1..V_Signed_Days Loop
             Loop
                If rtrim(ltrim(TO_CHAR(P_Date-Continus_Leave_Counter ,'DAY')) ) IN ('FRIDAY','SATURDAY') Then
                   Continus_Leave_Counter  := Continus_Leave_Counter  + 1;
                Else
                   Begin
                       Select HOLIDAY_DATE InTo V_Holiday_Date
                         From HXT_HOLIDAY_CALENDARS C, HXT_HOLIDAY_DAYS_VL D
                        Where C.ID=D.HCL_ID
                          And HOLIDAY_DATE = P_Date-Continus_Leave_Counter
                          And P_Date-Continus_Leave_Counter  BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
                       Continus_Leave_Counter  := Continus_Leave_Counter  + 1;
                       Exception
                       When No_Data_Found Then  Exit;
                       when others then Exit;                                --  10-APR-2004
                   End;
                End If;
             End Loop;
             Continus_Leave_Counter  := Continus_Leave_Counter  + 1;
         End Loop;
         -- signing the old recrod which are equl to or less than the signed day variable.
         For k In Signe_Records Loop
           Begin
             Update HXT_ATTENDANCE_AEC Set SIGGNED = 'Y', LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY =3    --change creation_date to last_update date suhaibani
              Where Trunc(Date_Time_In) <= Trunc(P_Date-Continus_Leave_Counter)
                And Business_Group_Id = k.Business_Group_Id
                And Organization_Id   = k.Organization_Id
                And Nvl(SIGGNED,'N') = 'N';
             If SQL%Found Then
                Declare                                                      -- start 10-APR-2004
                    V_Rec_Count number := 0;
                Begin
                   Select Nvl(Count(*),0) into V_Rec_Count
                     From HXT_AUTO_SIGNE_ATTENDANCE_AEC
                    Where BUSINESS_GROUP_ID = k.Business_Group_Id
                      And ORGANIZATION_ID   = k.Organization_Id
                      And trunc(DATE_TIME)  = trunc(P_Date-Continus_Leave_Counter);
                   If V_Rec_Count = 0 Then
                      Begin
                        Insert Into HXT_AUTO_SIGNE_ATTENDANCE_AEC (BUSINESS_GROUP_ID  , ORGANIZATION_ID  , DATE_TIME)
                               Values (k.Business_Group_Id, k.Organization_Id, trunc(P_Date-Continus_Leave_Counter));
                      Exception when others then null;
                      End;
                   End If;
                Exception when others then null;
                End;
             End If;
             Exception when others then null;
           End;                                                                  -- end 10-APR-2004
         End Loop;
     Exception when others then null;
     End Signe_Attendance;

-- this porcedure update the action table FLAG Field to 2 for yesterday records
    Procedure SET_YESTERDAY_RECORDS(P_Date In Date, P_EMP_NO In Varchar2, P_Memory_In In Date, P_SRecords Out Boolean) IS
    Begin
        Update HXT_ACTION_AEC Set Flag = '2'
         Where --Flag = '1' And
               EMP_NO = P_EMP_NO
           And To_Char(DATE_TIME,'DD-MON-YYYY') = To_Char(P_Date,'DD-MON-YYYY')   -- UpDate Todays Days Recrods For NextDay Processing
           And Date_Time >= P_Memory_In
           --And ACC_TYPE Not In (     -- Not Needed By Suhaibani 1-April-2007
        --Select LOOKUP_CODE FROM HXT_TIME_ATTENDANCE_STAT_AEC_V Y
         --Where P_Date Between NVL(Y.START_DATE_ACTIVE,P_Date) And NVL(Y.END_DATE_ACTIVE, P_Date)
        --  And NVL(ENABLED_FLAG,'Y') = 'Y' )
           And TERM_NO In (
        Select LOOKUP_CODE FROM HXT_TIME_ATTENDANCE_TERM_AEC_V Y
         Where P_Date Between NVL(Y.START_DATE_ACTIVE,P_Date) And NVL(Y.END_DATE_ACTIVE, P_Date)
           And NVL(ENABLED_FLAG,'Y') = 'Y' );
        If SQL%Found Then
           P_SRecords := True;
        End If;
        --Exception when others then message('A3 '||sqlcode||sqlerrm,acknowledge);
    End SET_YESTERDAY_RECORDS;
    -- Find last OUT in YESTERDAYS record
     --Re-writen by Suhaibani to exclude OUTGATE (main gates out) from the query
    Function Last_Out_Yesterday(P_Date Date, P_Emp_No In Varchar2) Return Date Is
    Date_Time_Out Date := Null;
    Begin
        Select A.DATE_TIME into DATE_TIME_OUT
        From HXT_ACTION_AEC A, HXT_TIME_ATTENDANCE_TERM_AEC_V V
        Where A.TERM_NO = V.LOOKUP_CODE
           And A.EMP_NO = P_EMP_NO
           And To_Char(DATE_TIME,'DD-MON-YYYY') = TO_CHAR(P_Date,'DD-MON-YYYY')
           And P_Date Between NVL(V.START_DATE_ACTIVE,P_Date) And NVL(V.END_DATE_ACTIVE, P_Date)
           And V.TAG='OUT'
           AND DATE_TIME= (Select MAX(DATE_TIME_OUT)
                                        From HXT_ACTION_RECS_AEC A
                                        Where A.EMP_NO = P_EMP_NO
                                              And PROC_DATE between trunc(P_DATE) and to_date(to_char(P_DATE,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS') -- to imptove peroframnce
                                              And DATE_TIME_Out between trunc(P_DATE) and to_date(to_char(P_DATE,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS'))
           AND DATE_TIME!= (Select MAX(DATE_TIME_IN)
                                        From HXT_ACTION_RECS_AEC A
                                        Where A.EMP_NO = P_EMP_NO
                                              And PROC_DATE between trunc(P_DATE) and to_date(to_char(P_DATE,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS') -- to imptove peroframnce
                                              And DATE_TIME_Out between trunc(P_DATE) and to_date(to_char(P_DATE,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS'))
           AND ROWNUM=1;

If Date_Time_Out is null then
           Return Null;
        Else
           Return Date_Time_Out;
        End If;
      Exception
        When NO_DATA_FOUND then
             Return Null;
    End Last_Out_Yesterday;
    -- Find 1st In in todays record
    Function Last_In_Yesterday(P_Date Date, P_Emp_No In Varchar2) Return Date Is
    Date_Time_In Date := Null;
    Begin
        Select MAX(DATE_TIME_IN) Into Date_Time_In
          From HXT_ACTION_RECS_AEC A
         Where A.EMP_NO = P_EMP_NO
           And To_Char(DATE_TIME_IN,'DD-MON-YYYY') = P_Date
           AND PROC_DATE Between P_DATE-1 and P_DATE+1; -- I put this line ONLY to improve performance by using the index.
        Return Date_Time_In;
        Exception
        When NO_DATA_FOUND Then
             Return Null;
    End Last_In_Yesterday;

-- Find last action in/out yesterday
    Function Last_Action_Yesterday(P_Date Date, P_Emp_No In Varchar2) Return Date Is
    Date_Time Date := Null;
    Begin
        Select MAX(DATE_TIME) Into Date_Time
          From HXT_ACTION_AEC A
         Where A.EMP_NO = P_EMP_NO
           And To_Char(DATE_TIME,'DD-MON-YYYY') = P_Date;
        Return Date_Time;
        Exception
        When NO_DATA_FOUND Then
             Return Null;
    End Last_Action_Yesterday;

-- Find 1st In in todays record
    Function Min_In_Today (P_Date Date, P_Emp_No In Varchar2) Return Date Is
    Date_Time_In Date := NULL;
    Begin
        Select MIN(DATE_TIME) Into Date_Time_In
          From HXT_ACTION_AEC A, HXT_TIME_ATTENDANCE_TERM_AEC_V V
         Where A.TERM_NO = V.LOOKUP_CODE
           --And A.FLAG = '1'
           And RTrim(LTrim(V.TAG)) like 'IN%'
           And A.EMP_NO = P_EMP_NO
           And trunc(DATE_TIME) = P_Date
           And P_Date Between NVL(V.START_DATE_ACTIVE,P_Date) And NVL(V.END_DATE_ACTIVE, P_Date);
           --And A.ACC_TYPE --= Y.LOOKUP_CODE(+) -- Not Needed By Suhaibani 1-April-2007
           --NOT IN (Select lookup_code from HXT_TIME_ATTENDANCE_STAT_AEC_V
                 --   Where P_Date Between START_DATE_ACTIVE And NVL(END_DATE_ACTIVE, P_Date) );
        Return Date_Time_In;
        Exception
        When NO_DATA_FOUND Then
             Return Null;
    End Min_In_Today;
    -- find the min in yesterady record
    Function FIND_IN_DATE_YESTERDAY(P_DATE In Date, P_EMP_NO In Varchar2) Return Date Is
    V_Date_Time_In      Date;
    Begin
        Select Min(A.DATE_TIME) Into V_Date_Time_In
          From HXT_ACTION_AEC A, HXT_TIME_ATTENDANCE_TERM_AEC_V V
         Where A.FLAG = '2'
           And A.TERM_NO = V.LOOKUP_CODE
           And V.TAG In ('IN','INGATE')
           And A.EMP_NO = P_EMP_NO
           And To_Char(DATE_TIME,'DD-MON-YYYY') = TO_CHAR(P_Date,'DD-MON-YYYY')
           And P_Date Between NVL(V.START_DATE_ACTIVE,P_Date) And NVL(V.END_DATE_ACTIVE, P_Date)
           --And A.ACC_TYPE --= Y.LOOKUP_CODE(+)  -- Not Needed By Suhaibani 1-April-2007
           --NOT IN (Select lookup_code from HXT_TIME_ATTENDANCE_STAT_AEC_V
                  --  Where P_Date Between START_DATE_ACTIVE And NVL(END_DATE_ACTIVE, P_Date) )
         Order By A.DATE_TIME;
        Return V_Date_Time_In;
        Exception
        When No_Data_Found Then Return Null;
    End FIND_IN_DATE_YESTERDAY;

--This Procedure Will Update the Action Table and replace the temporary card number with actual Employee Number
    Procedure UPDATE_TEMPORARY_CARDS(P_Date In Date, P_Emp_No In Varchar2) Is
    Begin -- updated by suhaibani to take issue date and return time into account.
        Update HXT_ACTION_AEC C Set C.Emp_No =  (Select B.EMPLOYEE_NUMBER
                                                 From HXT_TEMPORARY_CARD_ISSU_AEC A, PER_ALL_PEOPLE_F B
                                                 Where A.Person_Id=B.Person_Id
                                                  and A.TEMPORARY_CARD_NO=C.EMP_NO
                                                  And P_Date Between B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE
                                                  and C.DATE_TIME between trunc(A.ISSUED_DATE) and to_date(to_char(A.ISSUED_DATE,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS')
                                                  and rownum=1)
         Where Trunc(C.Date_Time)  = P_DATE
           And upper(replace(C.Emp_no,'-','')) In (
                        Select upper(replace(TEMPORARY_CARD_NO,'-',''))
                        From HXT_TEMPORARY_CARD_ISSU_AEC A, PER_ALL_PEOPLE_F B
                        Where A.Person_Id=B.Person_Id
                          And (
                                (B.Employee_Number = P_EMP_No and P_EMP_NO is not NULL )
                                OR
                                (P_EMP_NO is null)
                              )
                          And P_Date Between B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE
                          and C.DATE_TIME between trunc(A.ISSUED_DATE) and to_date(to_char(A.ISSUED_DATE,'DD-MM-RRRR')||' 23:59:59','DD-MM-RRRR HH24:MI:SS'));
        --Exception when others then message('A4 '||sqlcode||sqlerrm,acknowledge);
    End UPDATE_TEMPORARY_CARDS;

-- This procedure to load actions from APACS system using DAtabase link "AEC08"
    PROCEDURE LOAD_ACTION (p_date IN DATE,P_EMP_NO in varchar2 Default NULL) is
       Cursor Act_list IS
              SELECT EMP_NO,DATE_TIME,TERM_NO,ACTION_TYPE
              from daily_actions@apacs
              where trunc(DATE_TIME)=trunc(p_date)
              and (
                  (upper(replace(emp_no,'-','')) = upper(replace(P_EMP_NO,'-','')) and P_EMP_NO is not null)
                   OR
                  (P_EMP_NO is not null and upper(replace(emp_no,'-','')) =(select upper(replace(TEMPORARY_CARD_NO,'-',''))
                                                                           from HXT_TEMPORARY_CARD_ISSU_AEC a ,PER_ALL_PEOPLE_F b
                                                                           where a.person_id=b.person_id and
                                                                                 b.employee_number=P_EMP_NO and
                                                                                 trunc(a.ISSUED_DATE)=trunc(P_DATE) and
                                                                                 P_DATE between b.Effective_Start_Date And b.Effective_End_Date and
                                                                                 rownum=1))
                   OR
                  (emp_no IS NOT Null and P_EMP_NO is NULL)
                  );
       err_msg varchar2(100);
       V_EMP_NO varchar2 (20);
       BEGIN
          delete from hxt_action_bad_aec;
          For Act_list_rec in Act_list loop
          BEGIN
              if substr(Act_list_rec.EMP_NO,1,1) in ('0','1','2','3','4','5','6','7','8','9') then
                  insert into hxt_action_aec(EMP_NO,DATE_TIME,TERM_NO,ACC_TYPE)
                                                              values(Act_list_rec.EMP_NO
                                      ,Act_list_rec.DATE_TIME
                                  ,Act_list_rec.TERM_NO
                                           ,Act_list_rec.ACTION_TYPE);
               else -- TM TR CN CM ...etc
                   begin
                     select distinct a.employee_number into V_EMP_NO
                     from PER_ALL_PEOPLE_F a
                     where P_DATE Between a.Effective_Start_Date And a.Effective_End_Date AND
                           a.current_employee_flag='Y' and
                           upper(replace(a.employee_number,'-',''))=upper(replace(Act_list_rec.EMP_NO,'-',''));

exception
                        when others then
                            V_EMP_NO:=Act_list_rec.EMP_NO;
                   end;

insert into hxt_action_aec(EMP_NO,DATE_TIME,TERM_NO,ACC_TYPE)
                                                              values(V_EMP_NO
                                      ,Act_list_rec.DATE_TIME
                                  ,Act_list_rec.TERM_NO
                                           ,Act_list_rec.ACTION_TYPE);
              end if;
             --COMMIT;
           EXCEPTION
             WHEN others THEN
               err_msg:=substr(SQLERRM,1,100);
               insert into hxt_action_bad_aec(EMP_NO,DATE_TIME,TERM_NO,ACC_TYPE,MSG)
                               values(Act_list_rec.EMP_NO,
                          Act_list_rec.DATE_TIME,
                          Act_list_rec.TERM_NO,
                      Act_list_rec.ACTION_TYPE,
                      err_msg);
         -- COMMIT;
           END;
          END LOOP;
    END LOAD_ACTION;

-- procedure by suhaibani, 26-jun-2006, updated 28-nov 2006 to consider start and end date of assginments
  -- to be run within generate_attendance to build the HIERARCHY (all level) it is Recursive procedure.

Procedure BUILD_MGR_DEPT_HIERARCHY(P_MGR_ID IN Number,P_Sub_MGR_ID IN NUMBER, P_Level IN Number) is
    Cursor Mgr_list is -- GET ALL AEC MGR,ORG (any one who is supervising others)
      Select  B.Supervisor_ID MGR_ID , A.Organization_ID ORG_ID,min(B.Effective_Start_Date) START_DATE
      From HR_ORGANIZATION_UNITS A, PER_ALL_ASSIGNMENTS_F B, PER_ALL_PEOPLE_F D
      Where B.Organization_ID = A.Organization_ID And
            sysdate Between B.Effective_Start_Date And B.Effective_End_Date AND
            B.person_ID=D.person_ID AND
            sysdate Between d.Effective_Start_Date And d.Effective_End_Date AND
            D.current_employee_flag='Y'
     group by B.Supervisor_ID , A.Organization_ID;

Cursor Mgr_list_distinct is --GET ALL AEC MGR distinct (without org)
      Select  distinct B.Supervisor_ID MGR_ID
      From HR_ORGANIZATION_UNITS A, PER_ALL_ASSIGNMENTS_F B, PER_ALL_PEOPLE_F D
      Where B.Organization_ID = A.Organization_ID And
            sysdate Between B.Effective_Start_Date And B.Effective_End_Date AND
            B.person_ID=D.person_ID AND
            sysdate Between d.Effective_Start_Date And d.Effective_End_Date AND
            D.current_employee_flag='Y';

Cursor Sub_Mgr_list(Top_Mgr_ID number) is --get managers under given manager
      Select  distinct B.Supervisor_ID SUB_MGR_ID
      From HR_ORGANIZATION_UNITS A, PER_ALL_ASSIGNMENTS_F B, PER_ALL_PEOPLE_F D, PER_ALL_ASSIGNMENTS_F C
      Where B.Supervisor_ID=C.person_ID and
            C.Supervisor_ID=TOP_MGR_ID and
            sysdate Between C.Effective_Start_Date And C.Effective_End_Date AND
            B.Organization_ID = A.Organization_ID And
            sysdate Between B.Effective_Start_Date And B.Effective_End_Date AND
            B.person_ID=D.person_ID AND
            sysdate Between d.Effective_Start_Date And d.Effective_End_Date AND
            D.current_employee_flag='Y';

-- Direct Department under a sub manager
    Cursor Dept_Of_Sub_Mgr(Top_Mgr_ID number,Sub_Mgr_ID number) is
        select Top_Mgr_ID,A.ORG_ID,MIN(START_DATE) START_DATE
                 FROM HXT_MGR_DEPT_AEC A
                 WHERE A.MGR_LEVEL=0 and
                       A.MGR_PERSON_ID=Sub_Mgr_ID AND
                       CURRENT_FLAG='Y' AND
                       not exists (select 'x' from HXT_MGR_DEPT_AEC B where B.MGR_PERSON_ID=Top_Mgr_ID and B.ORG_ID=A.ORG_ID and B.MGR_LEVEL>0 and B.END_DATE is NULL AND CURRENT_FLAG='Y')
                 Group by Top_Mgr_ID,A.ORG_ID;

Begin
     IF P_LEVEL=0 then
       --  DELETE FROM HXT_MGR_DEPT_AEC; -- clear the table.
       UPDATE HXT_MGR_DEPT_AEC
       SET CURRENT_FLAG=NULL         -- clear falg for all valid (open) records.
       WHERE END_DATE is NULL;        -- when END_DATE!=NULL means that the record is history record.

For i In MGR_List Loop
            UPDATE HXT_MGR_DEPT_AEC      -- Get all managers and thier dept, if already exists then update flag, otherwise insert new record
            SET CURRENT_FLAG='Y'
            WHERE NVL(MGR_PERSON_ID,9999999)=NVL(i.MGR_ID,9999999) and ORG_ID=i.org_ID and MGR_LEVEL=P_LEVEL and END_DATE is NULL;
            IF NVL(SQL%ROWCOUNT,0)=0 then
               insert into HXT_MGR_DEPT_AEC(MGR_PERSON_ID,ORG_ID,MGR_LEVEL,START_DATE,CURRENT_FLAG) VALUES(i.MGR_ID,i.ORG_ID,P_LEVEL,i.START_DATE,'Y');
            END IF;

end loop;
        For k In Mgr_list_distinct Loop     -- Start builing hierarchy for every manager to find his in-direct dept.
            BUILD_MGR_DEPT_HIERARCHY(k.MGR_ID,k.MGR_ID,1);
        end loop;

-- This part will be reached by the first call of the procedure, non-reursive
        -- For non-active record --> set end_date
       UPDATE HXT_MGR_DEPT_AEC A -- for direct manager
       SET END_DATE=NVL((select max(B.Effective_END_Date)
                    from  PER_ALL_ASSIGNMENTS_F B
                    where B.SUPERVISOR_ID=A.MGR_PERSON_ID AND
                          B.Organization_ID=A.ORG_ID AND
                          sysdate > B.Effective_Start_Date AND
                          sysdate >=B.Effective_End_Date),TRUNC(SYSDATE))    --last modifed 7-1-2007
      WHERE CURRENT_FLAG is NULL AND END_DATE is NULL and MGR_LEVEL=0;

for l in 1..9 loop
        UPDATE HXT_MGR_DEPT_AEC A-- for non-direct manager
        SET END_DATE=NVL((select max(B.END_DATE)
                      FROM HXT_MGR_DEPT_AEC B,PER_ALL_ASSIGNMENTS_F C
                      WHERE B.MGR_LEVEL=A.MGR_LEVEL-1 AND
                            B.ORG_ID=A.ORG_ID AND
                            C.SUPERVISOR_ID=A.MGR_PERSON_ID AND
                            C.PERSON_ID=B.MGR_PERSON_ID),TRUNC(SYSDATE))
        WHERE CURRENT_FLAG is NULL AND END_DATE is NULL and MGR_LEVEL=l;
      end loop;

ELSE
       if P_MGR_ID!=P_Sub_MGR_ID then

--create Cursor here, try update if record already exists, otherwise insert...
          for d in Dept_Of_Sub_Mgr(P_MGR_ID,P_Sub_MGR_ID) loop
             --dbms_output.put_line('IN '||P_MGR_ID||'-'||P_Sub_MGR_ID||'-'||P_LEVEL);
             UPDATE HXT_MGR_DEPT_AEC
             SET CURRENT_FLAG='Y'
             WHERE MGR_PERSON_ID=d.Top_Mgr_ID and ORG_ID=d.Org_ID and MGR_LEVEL=P_LEVEL and END_DATE is NULL;
             IF NVL(SQL%ROWCOUNT,0)=0 then
                insert into HXT_MGR_DEPT_AEC(MGR_PERSON_ID,ORG_ID,MGR_LEVEL,START_DATE,CURRENT_FLAG) VALUES(d.Top_MGR_ID,d.ORG_ID,P_LEVEL,d.START_DATE,'Y');
             END IF;
          end loop;
       end if;

for j in SUB_MGR_LIST(P_Sub_MGR_ID) loop          --Recursive calls
           if P_MGR_ID!=P_Sub_MGR_ID then
              BUILD_MGR_DEPT_HIERARCHY(P_MGR_ID,j.SUB_MGR_ID,P_level+1);
              else BUILD_MGR_DEPT_HIERARCHY(P_MGR_ID,j.SUB_MGR_ID,P_level);
           end if;
       end loop;
      end if;
    End BUILD_MGR_DEPT_HIERARCHY;

-- 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
     -- get the global variable whose purpose is to find the employee sift gaps
     -- First Find IN time in previous date where FLAG = 2 . and save it in Memory_In else
     -- check Yesterday Records not found with Fland 2 and This is the first record of the loop and it OUT Record else
     -- check Yesterday Records not found with Fland 2 and This is the first record of the loop and it In Record
     -- then find the last out record
     -- If the last Status_Flag is IN this means that emploee record exist with two days
     -- else insert into HXT_ACTION_RECS_AEC
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) IS
     In_time  DATE  := Null;
     Out_Time   Date  := Null;
     Prev_In   Date   := Null;
     Prev_Outer   Date   := Null;
     V_Insert             Boolean    := False;
     V_Record_Exists      Boolean    := False;
     v_today_record_inserted    Boolean    := False;
     v_record_status     varchar2(30):=NULL;
     v_Last_in_Yesterday Date :=Last_in_Yesterday(P_DATE-1,P_EMP_NO);
     v_Last_Action_Yesterday Date :=Last_Action_Yesterday(P_DATE-1,P_EMP_NO);
     v_Last_out_Yesterday Date :=Last_out_Yesterday(P_DATE-1,P_EMP_NO);
     V_GAP_BETWEEN_OUT_IN NUMBER:=0;
     v_update_yesterday_record  Boolean    :=False;
     yesterday_shift_out  DATE := null;
     today_shift_in       DATE := P_Today_shift_in;
     v_dummy_flag         boolean:=null;

V_Date_Type             Varchar2(20);
    V_BBreak_In             Date;
    V_BBreak_Out            Date;
    V_ABreak_In             Date;
    V_ABreak_Out            Date;
    V_Shift_Name            Varchar2(80);
    V_Attendanc_Type        Varchar2(80);

-- This Cursor will select all records from HXT_ACTION_AEC table for a p_emo_no and for perticular date p_date
     Cursor Emp_Records Is
     Select A.EMP_NO, A.DATE_TIME, A.TERM_NO, A.ACC_TYPE, A.FLAG, V.TAG, A.ROWID
       From HXT_ACTION_AEC A, HXT_TIME_ATTENDANCE_TERM_AEC_V V
      Where A.TERM_NO = V.LOOKUP_CODE
        And A.EMP_NO = P_EMP_NO
        And To_Char(DATE_TIME,'DD-MON-YYYY') = TO_CHAR(P_Date,'DD-MON-YYYY')
        And P_Date Between NVL(V.START_DATE_ACTIVE,P_Date) And NVL(V.END_DATE_ACTIVE, P_Date)
        --And A.ACC_TYPE NOT IN (Select lookup_code from APPS.HXT_TIME_ATTENDANCE_STAT_AEC_V -- not neede By suhaibani
                               -- Where P_Date Between START_DATE_ACTIVE And NVL(END_DATE_ACTIVE, P_Date) )
        Order By A.DATE_TIME, V.TAG Desc;
     Begin

Begin
             -- it will get the global variable whose purpose is to find the employee shift gaps
             -- i.e if employee remains out of the office more than 60 minute (i.e. one hour) then
             -- system will consider this as new timing of the employee
             -- so now in time attendance empolyee will have two records in one day and so on
             SELECT To_Number(RTrim(Ltrim(Nvl(GLOBAL_VALUE,0)))) InTo V_GAP_BETWEEN_OUT_IN
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_GAP_BETWEEN_OUT_IN'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_GAP_Between_Out_In := 60;
         End;

--dbms_output.put_line(' - ');
         -- First Find IN time in previous date where FLAG = 2
         In_Time := FIND_IN_DATE_YESTERDAY(P_DATE-1, P_EMP_NO);

For m In Emp_Records Loop
             V_Record_Exists := True;
             If m.TAG like 'IN%' Then  --'IN' or 'INGATE'
                      If in_time is null Then
                         if Emp_Records%RowCount=1 and TIME_SUBTRACT(NVL(v_Last_out_Yesterday,trunc(P_DATE-1)),m.DATE_TIME) <=V_GAP_BETWEEN_OUT_IN and m.TAG!='INGATE' then
                            in_time:=v_last_in_yesterday;
                            v_update_yesterday_record:= true;
                          else
                            in_time:=m.DATE_TIME;
                         end if;
                         Out_Time:=null;
                      else -- in!=null
                         if Out_Time is null then
                            -- check if in_time is from yestrday then out_time=23:59 or shift_out
                            if Emp_Records%RowCount=1 and m.DATE_TIME > (today_shift_in-2/24)  and TIME_SUBTRACT(in_time,m.DATE_TIME)>510 then -->should we chrck for 60 gap only?
                               HXT_TIME_ATTENDANCE_AEC.GET_EMPLOYEE_SHIFT_TIME (P_Person_ID, P_Date-1, V_Date_Type,V_BBreak_In, V_BBreak_Out, V_ABreak_In, yesterday_shift_out, V_Attendanc_Type,V_Shift_Name);
                                  if in_time <= yesterday_shift_out and v_Last_Action_Yesterday <= yesterday_shift_out then
                                    out_time:=yesterday_shift_out;
                                  else out_time:=TO_DATE(to_char(in_time,'DD-MM-RRRR')||' 23:59:00','DD-MM-RRRR HH24:MI:SS');
                                  end if;
                                  v_record_Status:='Error1'; -- in without out
                                 v_update_yesterday_record:= true;
                                 prev_in:=m.DATE_TIME;
                                 v_insert:=true;
                            end if;
                         else -- out_time!= null
                                -- here, out_time is not in OUTER gate (not MAIN GATE OUT)
                           if TIME_SUBTRACT(out_time, m.DATE_TIME)> V_GAP_BETWEEN_OUT_IN or m.TAG='INGATE' then
                              prev_in:=m.DATE_TIME;
                              v_insert:=true;
                            else Out_time:=null;
                           end if;
                         end if;
                       end if;
             else -- TAG='OUT' OR ='OUTGATE'           -- check if gap between yestrday last out and today first out
                   If in_time is null Then
                         out_time:=m.DATE_TIME;
                         if Emp_Records%RowCount=1 and TIME_SUBTRACT(NVL(v_Last_out_Yesterday,trunc(P_DATE-1)),m.DATE_TIME)<= (12 * 60) then
                            in_time:=v_last_in_yesterday;
                            v_update_yesterday_record:= true;
                            if m.TAG='OUTGATE' then v_insert:=true; Prev_Outer:=m.DATE_TIME; end if; --get prev_outer to catch mutilple OUTER card readings case
                        -- elsif TIME_SUBTRACT(Prev_Outer,m.DATE_TIME) <1 then out_time:=null;  --catch mutilple OUTER card readings case
                         elsif ((m.DATE_TIME-Prev_Outer)*24*60) <1 then out_time:=null;  --TIME_SUBTRACT uses mintus only not seconds, catch mutilple OUTER card readings case
                      end if;
                   else -- in!=null
                          Out_time:=m.DATE_TIME;
                          if m.TAG='OUTGATE' then v_insert:=true; Prev_Outer:=m.DATE_TIME; end if;   --get prev_outer to catch mutilple OUTER card readings case
                   end if;
             end if;

if v_insert then
              -- insert into table in_time ..
                if TIME_SUBTRACT(in_time,out_time)<1440 then
                   Insert Into HXT_Action_Recs_AEC(EMP_NO, DATE_TIME_IN, DATE_TIME_OUT,PROC_DATE, Record_Status)
                   Values(m.EMP_NO, in_time, Out_time,P_Date ,v_record_Status);
                else
                   if v_record_Status is NULL then v_record_Status:='Error2'; end if; --long stay error
                   Insert Into HXT_Action_Recs_AEC(EMP_NO, DATE_TIME_IN, DATE_TIME_OUT,PROC_DATE, Record_Status)
                   Values(m.EMP_NO, in_time, In_time+(1/24)*23+59/(24*60),P_Date ,v_record_Status);
                   Insert Into HXT_Action_Recs_AEC(EMP_NO, DATE_TIME_IN, DATE_TIME_OUT,PROC_DATE, Record_Status)
                   Values(m.EMP_NO, in_time+1, Out_time,P_Date ,v_record_Status);
                end if;
              if trunc(out_time)!=trunc(P_DATE-1) then v_today_record_inserted:=true; end if;
              In_time:=prev_in;
              Out_time:=null;
              Prev_in:=null;
              v_insert:=false;
              v_record_Status:=Null;
             end if;
        end loop;
        if not V_Record_Exists then
           if In_Time is null then
              --insert 00
              in_time:=  trunc(P_DATE);
              out_time:= trunc(P_DATE);
              v_today_record_inserted:=true;
           else -- In_Time is not null
              --insert in_time :59:59 or shift_out
              HXT_TIME_ATTENDANCE_AEC.GET_EMPLOYEE_SHIFT_TIME (P_Person_ID, P_Date-1, V_Date_Type,V_BBreak_In, V_BBreak_Out, V_ABreak_In, yesterday_shift_out, V_Attendanc_Type,V_Shift_Name);
              if in_time <= yesterday_shift_out and v_Last_Action_Yesterday <= yesterday_shift_out then
                     out_time:=yesterday_shift_out;
                else out_time:=TO_DATE(to_char(in_time,'DD-MM-RRRR')||' 23:59:00','DD-MM-RRRR HH24:MI:SS'); --set OUTER
              end if;
              v_record_Status:='Error1'; -- in without out
              v_update_yesterday_record:= true;
           end if;
        else -- action records exist for today
           if in_time is not null and out_time is not null then
               --  insert into table in_time ..
               v_today_record_inserted:=true;
           elsif  in_time is not null then
                 --dbms_output.put_line('set_yestrday_record :'||in_time);
                 SET_YESTERDAY_RECORDS(P_DATE,P_EMP_NO,in_time,v_dummy_flag);
                 if trunc(in_time)=trunc(P_DATE-1) then
                    out_time:=Min_In_Today(P_DATE,P_EMP_NO); -- set OUTER
                    v_update_yesterday_record:= true;
                    v_record_Status:='Error2'; -- long stay
                 end if;
           elsif  out_time is not null then
               in_time:=out_time;
               v_today_record_inserted:=true;
               v_record_Status:='Error3'; -- Out without in --1) SET OUTER
           end if;
        end if;
        if in_time is not null and out_time is not null then
             if TIME_SUBTRACT(in_time,out_time)<1440 then
                   Insert Into HXT_Action_Recs_AEC(EMP_NO, DATE_TIME_IN, DATE_TIME_OUT,PROC_DATE, Record_Status)
                   Values(P_EMP_NO, in_time, Out_time,P_Date ,v_record_Status);
                   null;
              else
                   Insert Into HXT_Action_Recs_AEC(EMP_NO, DATE_TIME_IN, DATE_TIME_OUT,PROC_DATE, Record_Status)
                   Values(P_EMP_NO, in_time, In_time+(1/24)*23+59/(24*60),P_Date ,v_record_Status);
                   Insert Into HXT_Action_Recs_AEC(EMP_NO, DATE_TIME_IN, DATE_TIME_OUT,PROC_DATE, Record_Status)
                   Values(P_EMP_NO, in_time+1, Out_time,P_Date ,v_record_Status);
                   null;
             end if;
           v_record_Status:=Null;
        end if;
        if  not v_today_record_inserted then
        --   dbms_output.put_line('Z in: '||trunc(P_DATE)||' ----- out: '||trunc(P_DATE)||'      Record Status: '||v_record_status);
           In_time:=trunc(P_DATE);
           Out_time:=trunc(P_DATE);
           Insert Into HXT_Action_Recs_AEC(EMP_NO, DATE_TIME_IN, DATE_TIME_OUT,PROC_DATE, Record_Status)
           Values(P_EMP_NO, in_time, Out_Time,P_Date ,v_record_Status);
           v_record_Status:=Null;
        end if;

-- dbms_output.put_line(' - ');

P_update_yesterday_record:=v_update_yesterday_record;
     End GET_DATE_TIME_IN_OUT;

-- Procedure to Delete attendance of an employee for a certian day BY SUHAIBANI
Procedure PURGE_ATTENDANCE(P_Date In Date, P_Emp_No In Varchar2, ALL_EMP in Boolean) IS
  v_person_id number:=null;
  BEGIN
        Begin
             SELECT person_id into v_person_id
             from PER_ALL_PEOPLE_F A
             where -- employee_number=P_EMP_NO --suhaibani 24-12-2006
             upper(replace(employee_number,'-','')) = upper(replace(P_EMP_NO,'-',''))
             and P_Date BETWEEN A.EFFECTIVE_START_DATE   AND A.EFFECTIVE_END_DATE
             and rownum=1 ;
        Exception
             When Others Then
                  v_person_id := 0;
        End;

if NVL(ALL_EMP,false) then
      DELETE HXT_ACTION_AEC WHERE  TRUNC(DATE_TIME)=TRUNC(P_DATE);
      DELETE HXT_Action_Recs_AEC WHERE  TRUNC(PROC_DATE)=TRUNC(P_DATE);
      DELETE HXT_ATTENDANCE_AEC WHERE  TRUNC(DATE_TIME_OUT)=TRUNC(P_DATE);
      DELETE HXT.HXT_OVERTIME_AEC WHERE TRUNC(DATE_TIME_OUT)=TRUNC(P_DATE);
    else
      DELETE HXT_ACTION_AEC WHERE upper(replace(EMP_NO,'-','')) = upper(replace(NVL(P_EMP_NO,0),'-','')) AND TRUNC(DATE_TIME)=TRUNC(P_DATE);
      DELETE HXT_Action_Recs_AEC WHERE upper(replace(EMP_NO,'-','')) = upper(replace(NVL(P_EMP_NO,0),'-','')) AND TRUNC(PROC_DATE)=TRUNC(P_DATE);
      DELETE HXT_ATTENDANCE_AEC WHERE PERSON_ID=V_PERSON_ID AND TRUNC(DATE_TIME_OUT)=TRUNC(P_DATE);
      DELETE HXT.HXT_OVERTIME_AEC WHERE PERSON_ID=V_PERSON_ID AND TRUNC(DATE_TIME_OUT)=TRUNC(P_DATE);
    end if;
  END PURGE_ATTENDANCE;
  
  PROCEDURE load_overtime(P_Person_Id In Number, P_Period_Start In Date, P_Period_End In Date) IS
cursor c1 is
  SELECT   organization_id, att.person_id,  MIN(date_time_in) DateTimeIn,
         MAX(date_time_out) DateTimeOut,
         SUBSTR(CAST(numtodsinterval(Max(date_time_out)-Min(date_time_in),'day') as interval day(2) to second(0)),5,8) TOTAL_HOURS ,
         SHIFT_TYPE, STATUS, REMARKS,
         DECODE(sum(regular_overtime_approved),0, sum(special_overtime_approved), SUM(REGULAR_OVERTIME_APPROVED)) OT
       FROM HXT.HXT_ATTENDANCE_AEC ATT
      WHERE TRUNC(DATE_TIME_IN) between  P_Period_Start  and  P_Period_End 
        AND (NVL(regular_overtime_approved,0) > 0 OR  nvl(special_overtime_approved,0) > 0)
        AND    att.person_id = p_person_id
        group by organization_id, att.person_id, TRUNC(DATE_TIME_IN), TRUNC(DATE_TIME_OUT), SHIFT_TYPE, STATUS, REMARKS;
        
        lstring          VARCHAR2( 500 );
        lstring1          VARCHAR2( 500 );
        
begin

for rec in c1
loop

DELETE FROM HXT.HXT_OVERTIME_AEC 
     WHERE TRUNC(DATE_TIME_IN) between P_Period_Start and P_Period_End
      AND WFAPPROVAL_STATUS IS NULL
      and person_id = P_Person_Id;
      
    COMMIT;

INSERT INTO HXT.HXT_OVERTIME_AEC
         (organization_id,person_id,  date_time_in,
         date_time_out, total_hours_worked, shift_type,
         Status, remarks,
         overtime_hours)
      values
         (rec.organization_id, rec.person_id, rec.datetimein,
         rec.datetimeout, rec.total_hours, rec.shift_type,
         rec.status, rec.remarks,
         rec.ot
         );
         
         lstring := rec.person_id||'-'||rec.datetimein||'-'||rec.datetimeout||'-'||rec.total_hours||'-'||rec.ot;
         
         FND_FILE.PUT_LINE(FND_FILE.LOG,'Overtime Inserted' ||rec.total_hours||'-'||rec.ot);

end loop;
commit;

EXCEPTION
WHEN OTHERS THEN NULL;
  /*errbuff := 'Following Error Encountered -- ';
  errbuff := errbuff || 'Oracle Error: ' || TO_CHAR (SQLCODE);
  errbuff := errbuff || ' Oracle Message: ' || sqlerrm;
  errbuff := errbuff;
  retcode := 2;
  fnd_file.put_line (fnd_file.log, errbuff);*/
end load_overtime;

End HXT_TIME_ATTENDANCE_AEC;

Texto modificado

Abrir archivo

/*<TOAD_FILE_CHUNK>*/
CREATE OR REPLACE PACKAGE BODY APPS."HXT_TIME_ATTENDANCE_AEC" as
/****************************************************************************************/
-- This is the main package which will run daily basis and calulate the time attandenc
-- of the 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
-- 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
-- As on the basis of date type employees shift type is selected from employee plan
-- Now get the Employees Current Shift Timings i.e
-- Before Break In , Before Break Out, After Break In, After Break Out in date format
-- DD-MON-YYYY HH24:MI.
-- 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
-- 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
-- dual days.
-- 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
-- no record will be enterd. and next day his current records and prevuios day dual day
-- record will be enterd in HXT_ATTENDANCE_AEC.
-- 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
-- in HXT_ATTENDANCE_AEC.
-- 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
-- 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
-- and some information is enterd in log file of the process
-- at the end of the loop
-- 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
-- Then in the last after all loops ends system will
-- auto sign the attendance for 5 days back records which are not signed
/*********************************************************************************/
/* CLEANINIG: CODE CLEANED OUT on 13-May-2007 By Suhaibani          */
/* commented old useless code were removed     */

-- 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);

-- 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,
                                                    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_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);
         -- 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, 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);                                                    
                                                    
                                                    
         else
            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,
                        V_Yesterday_BBreak_In, V_Yesterday_BBreak_Out, V_Yesterday_ABreak_In, V_Yesterday_ABreak_Out, V_Yesterday_Attendanc_Type,
                        V_Yesterday_Shift_Name);
            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_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_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.
        end if;
         If V_update_yesterday_record Then
         -- 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));
         End If;
         --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
          --dbms_output.put_line(to_char(V_DATE_TIME_IN,'DD HH24:MI'));
         BEGIN
            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) -- added creation date suhaibani
            VALUES
            (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,
            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),
            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);
         exception
           when DUP_VAL_ON_INDEX then null;
         END;

-- 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,
                                                    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_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

if V_MUSLIM!='True' then V_Date_Type := 'Normal'; end if;
         Else
            V_Date_Type := 'Normal';
         End If;
         Return V_Date_Type;
     End GET_DATE_TYPE;
      -- To get the Employee Attendanc Type i.e Standr Shift, Variable etc
      -- 1st its check emp Current shift entry in 'Chang Work Shift' For the current process date
      -- If not found the Shift in 'Change Work Shift' Then it checks in monthly schedule
      -- it shift not find in change work shift and in monthly schedule then it will get the work plan 'OTA Submit Employee'
      -- on the basis of Work Plan System will get the Current Shift Name of the Emplooye for that particular Day
      -- and in the last it return the before break in, befor brak out , after break in and after brak out
     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) IS
     V_Shift_Found Boolean := False;
     V_Work_Plan   Varchar2(60);
     V_Date_Type   Varchar2(20);         --suhaibani 27-12-09
     V_BBreak_In   Varchar2(80);
     V_BBreak_Out  Varchar2(80);
     V_ABreak_In   Varchar2(80);
     V_ABreak_Out  Varchar2(80);
     V_Grade  Varchar2(10);
     V_Count     Number;
     V_Monthly_Schedule Varchar2(7):='False';
     Begin
     -- the following (if statement) added by Suhaibani 27-12-09 to correct the problem of defaulting "out time" to 17:00 when there is no out action in that day, 
     -- it happened because P_DATE_TYPE was not set. Now V_Date_type is updated and used thenafter in this procedure.
     if P_Date_Type is Null then
        V_Date_Type:=GET_DATE_TYPE(P_PERSON_ID,P_Date);
     else
        V_Date_Type:=P_Date_Type;
     end if;
     
         Begin
      -- get the Employee Attendancy Type i.e Standart Shift or Variable Shift etc
             Select ATTENDANCE_TYPE
               InTo P_Attendance_Type
               From PER_PERSON_ANALYSES A, HXT_OTA_INFO_AEC B
              Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
                And A.PERSON_ID = P_Person_ID
                And P_Date Between NVL(A.DATE_FROM,P_Date) AND NVL(A.DATE_TO,P_Date);
             Exception
             When No_Data_Found Then
                  Begin --By Suhaibani, if grade is 10+, then attendance type is SF (flex time)
                     Select b.name
                     INTO v_Grade
                     FROM PER_ALL_ASSIGNMENTS_F A,PER_GRADES B
                     Where a.GRADE_ID=b.GRADE_ID AND
                           A.PERSON_ID=P_Person_ID AND
                           P_Date between a.EFFECTIVE_START_DATE   and a.EFFECTIVE_END_DATE;

-- This condition has been added temporarily by atallaha at 22-Aug-2009 to stop flexible time for the non Muslims employee in Ramadan month
                  Select Count(*) Into V_Count
                       From PAY_USER_TABLES A,
                            PAY_USER_COLUMNS B, PAY_USER_ROWS_F C, PAY_USER_COLUMN_INSTANCES_F D,
                            PAY_USER_COLUMNS E, PAY_USER_ROWS_F F, PAY_USER_COLUMN_INSTANCES_F G
                      Where A.USER_TABLE_ID = B.USER_TABLE_ID
                        AND A.USER_TABLE_ID = C.USER_TABLE_ID
                        AND B.USER_COLUMN_ID= D.USER_COLUMN_ID
                        AND C.USER_ROW_ID   = D.USER_ROW_ID
                        AND A.USER_TABLE_ID = E.USER_TABLE_ID
                        AND A.USER_TABLE_ID = F.USER_TABLE_ID
                        AND E.USER_COLUMN_ID= G.USER_COLUMN_ID
                        AND F.USER_ROW_ID   = G.USER_ROW_ID
                        AND P_Date BETWEEN C.EFFECTIVE_START_DATE   AND C.EFFECTIVE_END_DATE
                        AND P_Date BETWEEN D.EFFECTIVE_START_DATE   AND D.EFFECTIVE_END_DATE
                        AND P_Date BETWEEN F.EFFECTIVE_START_DATE   AND F.EFFECTIVE_END_DATE
                        AND P_Date BETWEEN G.EFFECTIVE_START_DATE   AND G.EFFECTIVE_END_DATE
                        AND A.USER_TABLE_NAME = 'HXT Ramadan Calendar AEC'
                        AND B.USER_COLUMN_NAME = E.USER_COLUMN_NAME
                        AND RTRIM(LTRIM(C.ROW_LOW_RANGE_OR_NAME)) LIKE 'Start Date'
                        AND RTRIM(LTRIM(F.ROW_LOW_RANGE_OR_NAME)) LIKE 'End Date'
                        AND P_Date Between Trunc(To_Date(D.Value,'YYYY/MM/DD HH24:MI:SS')) And
                                        Trunc(To_Date(G.Value,'YYYY/MM/DD HH24:MI:SS'));              
                   
                    IF to_number(v_Grade)>=10 And not V_Count =1 THEN
                        P_Attendance_Type := 'SF';
                    ELSE
                        P_Attendance_Type := 'SS';
                     END IF;
                     
                    Exception
                        When Others Then P_Attendance_Type := 'SS';
                  End;
             When Too_Many_Rows Then
                  P_Attendance_Type := 'SS';
             When Others Then
                  P_Attendance_Type := 'SS';
         End;
        /* -- In Ramadan No Flex Time
         IF V_DATE_TYPE='Ramadan' AND(P_Attendance_Type='SF' OR P_Attendance_Type='SR')THEN
             P_Attendance_Type:='SS';
         END IF;*/

If P_Shift_Name Is Null Then
            V_Shift_Found := True;
            Begin
      -- 1st its check emp shift entry in 'Chang Work Shift' For the current process date
                Select B.SEGMENT3 InTo P_Shift_Name
                  From PER_PERSON_ANALYSES A, PER_ANALYSIS_CRITERIA  B, FND_ID_FLEX_STRUCTURES C
                 Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
                   And A.PERSON_ID = P_Person_Id
                   And A.ID_FLEX_NUM = C.ID_FLEX_NUM
                   And C.ID_FLEX_STRUCTURE_CODE = 'CHANGE_WORK_SHIFT'
                   And P_Date Between To_Date(B.SEGMENT1,'DD-MON-YYYY') And
                                      To_Date(B.SEGMENT2,'DD-MON-YYYY')
                   And P_Date Between nvl(A.DATE_FROM,P_Date) and nvl(A.DATE_TO,P_Date) ;
                Exception
                When No_Data_Found Then
                     V_Shift_Found := False;
                When Too_Many_Rows Then
                     V_Shift_Found := False;
                When Others Then
                     V_Shift_Found := False;
            End;
            If Not V_Shift_Found Then
               V_Shift_Found := True;
               Begin
      -- If not found the Shift in 'Change Work Shift' Then it checks in 'Monthly Schedule'
                   Select TAG,'True'
                     InTo P_Shift_Name,V_Monthly_Schedule
                     From FND_LOOKUP_VALUES A, HXT_MONTHLY_SCHEDULE_AEC B
                    Where A.LOOKUP_CODE = B.SHIFT_TIME
                      And B.PERSON_ID = P_Person_ID
                      And B.DUTY_DATE = P_Date
                      And A.Lookup_Type = 'HXT_SEC_GUARD_LOCATIONS_AEC'
                      And A.Language = 'US';
                   Exception
                   When No_Data_Found Then
                        V_Shift_Found := False;
                   When Too_Many_Rows Then
                        V_Shift_Found := False;
                   When Others Then
                        V_Shift_Found := False;
               End;
            End If;
         End If;
         If Not V_Shift_Found Then
            V_Shift_Found := True;
            If P_Shift_Name Is Null Then
               Begin
              -- it shift not find in change work shift and in monthly schedule then it will get the work plan 'OTA Submit Employee'
                   Select Decode(V_Date_Type,'Normal',NORMAL_WORK_PLAN, RAMADAN_WORK_PLAN)
                     InTo V_Work_Plan
                     From PER_PERSON_ANALYSES A, HXT_OTA_INFO_AEC B
                    Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
                      And A.PERSON_ID = P_Person_ID
                      And P_Date Between NVL(A.DATE_FROM,P_Date) AND NVL(A.DATE_TO,P_Date);
                   Exception
                   When No_Data_Found Then Null;
                   When Too_Many_Rows Then NULL;
                   When Others Then null;
               End;
               Begin
               -- it will get the Current 'Work Shift' of the employee against the Work plan found in V_WORK_PLAN
               -- for the Current Processing Date
                   -- 28-Apr-2004  If Employee Record not found in any shfit type then
                   If V_Work_Plan Is Null Then
                      IF V_DATE_TYPE='Normal' THEN
                            V_Work_Plan := 'Normal Morning Shift';
                       ELSE V_Work_Plan := 'Ramadan Normal Morning Shift';
                      END IF;
                   End If;
                   ---- end ----
                   Select D.VALUE Into P_Shift_Name
                     From PAY_USER_TABLES A,
                          PAY_USER_COLUMNS B, PAY_USER_ROWS_F C, PAY_USER_COLUMN_INSTANCES_F D
                    Where A.USER_TABLE_ID = B.USER_TABLE_ID
                      AND A.USER_TABLE_ID = C.USER_TABLE_ID
                      AND B.USER_COLUMN_ID= D.USER_COLUMN_ID
                      AND C.USER_ROW_ID   = D.USER_ROW_ID
                      AND P_Date BETWEEN C.EFFECTIVE_START_DATE   AND C.EFFECTIVE_END_DATE
                      AND P_Date BETWEEN D.EFFECTIVE_START_DATE   AND D.EFFECTIVE_END_DATE
                      AND A.USER_TABLE_NAME = 'HXT Work Plan AEC'
                      AND B.USER_COLUMN_NAME = V_Work_Plan
                      AND RTRIM(LTRIM(C.ROW_LOW_RANGE_OR_NAME)) = LTRIM(RTRIM(To_Char(P_Date,'Day')));
                   Exception
                   When No_Data_Found Then Null;
                   When Too_Many_Rows Then NULL;
                   When Others Then null;
               End;
            End If;
         End If;
                        
        IF P_Attendance_Type = 'SF' and P_Shift_Name like 'Mid Morning Shift' THEN --added by hadba to filter RH office employees
        P_Attendance_Type := 'SR';
        end if;
        -- In Ramadan No Flex Time
        IF V_DATE_TYPE='Ramadan' AND(P_Attendance_Type='SF' OR P_Attendance_Type='SR')THEN --moved by hadba
            P_Attendance_Type:='SS';
        END IF;
         -- Suhaibani 15 Nov 2006
         -- If it is "Offical" Holiday (Eid or National Day) and The employee is NOT Security Gurad (Monthly Schedule) then reset his attendance to "Off Shift"
         IF IS_WEEKEND_HOLIDAY(P_Date)='True' and TO_char(P_DATE,'Dy')!='Thu' and TO_char(P_DATE,'Dy')!='Fri' and V_Monthly_Schedule!='True' then
            P_Shift_Name:='Off Shift';
         end if;

-- will get the tardiness and 1/2 day absentism of the employee
      -- get the global variables
      -- calculate the tardiness of the employee
      -- check the minimum ovetime restriction before and after shift
      -- make tardiness 0 for Flex and Variable Shift Type
      -- converting 'Offical Work Hours' and 'Total Work Hours' into Date Format
     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) IS
     V_LateFlag       Boolean  := False;
     V_ATotal_Hours   Number   := 0;
     V_Official_Hours Number   := 0;
     V_Hours_Minutes  Varchar2(80);
     V_MyString       Varchar2(100);
     V_Late_Start     Number;
     V_Early_End      Number;
     V_AEC_MAXIMUM_LATE_ALLOWED Number;
     V_BEFORESHIFT_OVERTIME  Number;
     V_AFTERSHIFT_OVERTIME   Number;
     V_FLEX_SHIFT_LATE_START Number;
     V_FLEX_SHIFT_EARLY_END  Number;
     V_RH_FLEX_SHIFT_LATE_START Number; --by hadba
     V_RH_FLEX_SHIFT_EARLY_END  Number; --by hadba
     Begin          --AEC_FLEX_SHIFT_LATE_START AEC_FLEX_SHIFT_EARLY_END
         Begin
             --its 45 minutes normally in AEC
             -- There is a relaxsation for flexable shift type of 60 minutes in AEC for Start Timings
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo  V_FLEX_SHIFT_LATE_START
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_FLEX_SHIFT_LATE_START'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_FLEX_SHIFT_LATE_START := 45;
         End;
         Begin
             --its 45 minutes normally in AEC
             -- There is a relaxsation for flexable shift type of 60 minutes in AEC for leaving office early
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo  V_FLEX_SHIFT_EARLY_END
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_FLEX_SHIFT_EARLY_END'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_FLEX_SHIFT_EARLY_END := 45;
         End;
-------------------------------------------------------------------------------------------------------------------by hadba
         Begin
              --Riyadh office flextime
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo  V_RH_FLEX_SHIFT_LATE_START
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_RH_FLEX_SHIFT_LATE_START'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_RH_FLEX_SHIFT_LATE_START := 30;
         End;
         Begin
             --Riyadh office flextime
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo  V_RH_FLEX_SHIFT_EARLY_END
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_RH_FLEX_SHIFT_EARLY_END'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_RH_FLEX_SHIFT_EARLY_END := 30;
         End;
-------------------------------------------------------------------------------------------------------------------
         Begin
             --its 15 minutes normally in AEC
             -- There is a relaxsation of 15 minutes in AEC for Start Timings
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo V_Late_Start
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_LATE_START'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             V_Early_End := V_Late_Start;
             Exception
             When Others Then
                  V_Late_Start := 15;
                --V_Late_Start := 15;
                  V_Early_End := 15;
         End;
         Begin
             --its 120 minutes normally in AEC
             -- maximum late an employee can be is 120 minutes i.e 2 hours
             SELECT To_Number(RTrim(LTrim(Nvl(GLOBAL_VALUE,0)))) InTo V_AEC_MAXIMUM_LATE_ALLOWED
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_MAXIMUM_LATE_ALLOWED'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_AEC_MAXIMUM_LATE_ALLOWED := 120;
         End;
         Begin
             -- its 30 minutes normally in AEC
             -- minimum overtime before the shift is 30 minutes in AEC
             SELECT To_Number(RTrim(Ltrim(Nvl(GLOBAL_VALUE,0)))) InTo V_BEFORESHIFT_OVERTIME
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_BEFORESHIFT_OVERTIME_ALLOWED'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                    V_BEFORESHIFT_OVERTIME:= 30;
         End;
         Begin
             -- its 30 minutes normally in AEC
             -- minimum overtime after the shift is 30 minutes in AEC
             SELECT To_Number(RTrim(Ltrim(Nvl(GLOBAL_VALUE,0)))) InTo V_AFTERSHIFT_OVERTIME
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_AFTERSHIFT_OVERTIME_ALLOWED'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                    V_AFTERSHIFT_OVERTIME := 30;
         End;
      V_ATotal_Hours    := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
      P_OverTime_Deduct := 0;
      P_Late_Start      := V_Late_Start;
      -- for flexable shift type late start and early end is assigned
      If P_Attendance_Type = 'SF' Then
           V_Late_Start :=  V_Flex_Shift_Late_Start;
           V_Early_End  :=  V_Flex_Shift_Early_end;
           P_Late_Start      := 0; --by suhaibani 12-dec-2006, No 15 Min excuse with Flextime as per policy
     ---------------------------------------------------------------------------------------------------------------by hadba
      ELSIF P_Attendance_Type = 'SR' Then 
           V_Late_Start :=  V_RH_FLEX_SHIFT_LATE_START;
           V_Early_End  :=  V_RH_FLEX_SHIFT_EARLY_END;
           V_Flex_Shift_Late_Start :=  V_RH_FLEX_SHIFT_LATE_START;
           V_Flex_Shift_Early_end  :=  V_RH_FLEX_SHIFT_EARLY_END;
           P_Late_Start      := 0;
     ---------------------------------------------------------------------------------------------------------------
      else
           V_Flex_Shift_Late_Start := 0;
           V_Flex_Shift_Early_end  := 0;
      End If;
      -- if employee not come during its shift timings then Official Work Hours are 0
      If P_Date_Time_In > P_ABreak_Out Or P_Date_Time_Out < P_BBreak_In Then
         V_Official_Hours := 0;
         V_ATotal_Hours := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
      Else
      -- Here system is computing the tardiness and 1/2 day lateism
         -- Employee come after the break and go after the time
         If P_Date_Time_In >= P_ABreak_In  And P_Date_Time_Out >= P_ABreak_Out THEN
            V_Official_Hours:= TIME_SUBTRACT(P_Date_Time_In, P_ABreak_Out);
            V_ATotal_Hours  := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
               /*If TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                    P_Todays_Absent :=Nvl( P_Todays_Absent,0) + 0.5;
               ElsIf*/
            If TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In) > V_Late_Start THEN
               P_Tardiness     := TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In)-V_Flex_Shift_Late_Start;
            End If;
            P_Todays_Absent := 0.5;
         -- Employee come after the break and go before the time out
         ElsIf P_Date_Time_In >= P_ABreak_In  And P_Date_Time_Out < P_ABreak_Out THEN
            V_Official_Hours:= TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            V_ATotal_Hours  := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            P_Todays_Absent := 0.5;
            If TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In) > V_Late_Start THEN
               P_Tardiness     := TIME_SUBTRACT(P_ABreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start;
            End If;
            If TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_Early_End THEN
              -- P_Tardiness := NVL(P_Tardiness,0) + TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) - V_Flex_Shift_Early_End;
              null;        --changed by suhaibani, to not count early go
            End If;
         -- Employee come in the break and  go after the time out
         ElsIf P_Date_Time_In >= P_BBreak_Out And P_Date_Time_Out >= P_ABreak_Out THEN
            V_Official_Hours:= TIME_SUBTRACT(P_ABreak_In, P_ABreak_Out);
            V_ATotal_Hours  := TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            P_Todays_Absent := 0.5;
            P_Tardiness     := 0;
         -- Employee in  break and go after or on the break finish time
         ElsIf P_Date_Time_In >= P_BBreak_Out And P_Date_Time_Out >= P_ABreak_In  THEN
            V_Official_Hours:= TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            V_ATotal_Hours  := V_Official_Hours;
            P_Todays_Absent := 0.5;
            P_Tardiness     := 0;
            If TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_Early_End THEN
              -- P_Tardiness := NVL(P_Tardiness,0) + TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) - V_Flex_Shift_Early_End;
              null;        --changed by suhaibani, to not count early go
            End If;
         -- Employee come in  the break and  go before the break finishes
         ElsIf P_Date_Time_In >= P_BBreak_Out And P_Date_Time_Out < P_ABreak_In  THEN
            V_Official_Hours:= 0;
            V_ATotal_Hours  := 0;
            P_Todays_Absent := 1;
            P_Tardiness     := 0;
         -- Employee come before the break and go after the time out
         ElsIf P_Date_Time_In >= P_BBreak_In And P_Date_Time_Out >= P_ABreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_out);
            V_ATotal_Hours   := V_Official_Hours;
            V_Official_Hours := V_Official_Hours + TIME_SUBTRACT(P_ABreak_In, P_ABreak_out);
            V_ATotal_Hours   := V_ATotal_Hours + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_out);
               If  TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := 0.5;
                P_Tardiness     := 0;
            ElsIf TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_Late_Start THEN
                P_Todays_Absent := 0;
                P_Tardiness     := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start;
            Else
                P_Todays_Absent := 0;
                P_Tardiness     := 0;
            End If;
         -- Employee come before the break and go before the time out
         ElsIf P_Date_Time_In >= P_BBreak_In And P_Date_Time_Out >= P_ABreak_In Then
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_out);
            V_ATotal_Hours   := V_Official_Hours;
            V_Official_Hours := V_Official_Hours + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_out);
            V_ATotal_Hours   := V_ATotal_Hours + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_out);
               If  TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := 0.5;
                P_Tardiness     := 0;
            ElsIf TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_Late_Start THEN
                P_Todays_Absent := 0;
                P_Tardiness     := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start;
            End If;
               If  TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := P_Todays_Absent + 0.5;
            ElsIf TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_Early_End THEN
                -- P_Tardiness     := P_Tardiness + TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) - V_Flex_Shift_Early_End;
              null;        --changed by suhaibani, to not count early go
            End If;
         -- Employee come before the break and go in the break
         ElsIf P_Date_Time_In >= P_BBreak_In And P_Date_Time_Out >= P_BBreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_out);
            V_ATotal_Hours   := V_Official_Hours;
             If  TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := 0.5;
                P_Tardiness     := 0;
          ElsIf TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_Late_Start THEN
                P_Todays_Absent := 0;
                P_Tardiness     := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start; --due to going in break
          End If;
          P_Todays_Absent := P_Todays_Absent + 0.5; --- then absent is "1" what about official hours
         -- Employee come before the break and go before the break
         ElsIf P_Date_Time_In >= P_BBreak_In And P_Date_Time_Out < P_BBreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            V_ATotal_Hours   := V_Official_Hours;
               If  TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_AEC_MAXIMUM_LATE_ALLOWED THEN
                P_Todays_Absent := 0.5;
                P_Tardiness     := 0;
            ElsIf TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) > V_Late_Start THEN
                P_Todays_Absent := 0;
                P_Tardiness     := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_In) - V_Flex_Shift_Late_Start;
            Else
                P_Todays_Absent := 0;
                P_Tardiness     := 0;
            End If;
            P_Todays_Absent := P_Todays_Absent + 0.5;
         -- Empoyee come before time In and go after time Out
         ElsIf P_Date_Time_In < P_BBreak_In And P_Date_Time_Out >= P_ABreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_BBreak_In, P_BBreak_Out)+ TIME_SUBTRACT(P_ABreak_In, P_ABreak_Out);
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_Out) + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            P_Todays_Absent := 0;
            P_Tardiness     := 0;
         -- Empoyee come before time In and go before Time Out
         ElsIf P_Date_Time_In < P_BBreak_In And P_Date_Time_Out >= P_ABreak_In Then
            V_Official_Hours := TIME_SUBTRACT(P_BBreak_In, P_BBreak_Out)+ TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_Out) + TIME_SUBTRACT(P_ABreak_In, P_Date_Time_Out);
            If TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) > V_Early_End THEN
               -- P_Tardiness := TIME_SUBTRACT(P_Date_Time_Out, P_ABreak_Out) - V_Flex_Shift_Early_End;
              null;        --changed by suhaibani, to not count early go
            End If;
         -- Empoyee come before time In and go in or after the Break
         ElsIf P_Date_Time_In < P_BBreak_In And P_Date_Time_Out >= P_BBreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_BBreak_In, P_BBreak_Out);
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_Out);
            P_Todays_Absent  := 0.5;
            P_Tardiness      := 0;
         -- Empoyee come before time In and go before the break
         ElsIf P_Date_Time_In < P_BBreak_In And P_Date_Time_Out < P_BBreak_Out Then
            V_Official_Hours := TIME_SUBTRACT(P_BBreak_In, P_Date_Time_Out);
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            P_Todays_Absent  := 0.5;
            P_Tardiness      := 0;
         End If;
         -- Check the minimum ovetime before the shift
         If P_Date_Time_In < P_BBreak_In Then
               If TIME_SUBTRACT(P_Date_Time_In, P_BBreak_In) < Nvl(V_BeforeShift_OVERTIME,0) Then
                    P_OverTime_Deduct := TIME_SUBTRACT(P_Date_Time_In, P_BBreak_In);
            End If;
         End If;
         -- Check the minimum ovetime after the shift
         If P_Date_Time_Out > P_ABreak_Out Then
               If TIME_SUBTRACT(P_ABreak_Out, P_Date_Time_Out) < Nvl(V_AfterShift_OVERTIME,0) Then
                    P_OverTime_Deduct := Nvl(P_OverTime_Deduct,0) + TIME_SUBTRACT(P_ABreak_Out, P_Date_Time_Out);
               End If;
         End If;
      End If;
         -- if employee shift type is Variable and Flexable Then not Tardiness is computed
         If P_Attendance_Type IN ('SV') Then
            V_ATotal_Hours   := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            V_Official_Hours := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            If P_Date_Time_In > P_ABreak_Out Or P_Date_Time_Out < P_BBreak_In Then
               V_Official_Hours := 0;
               V_ATotal_Hours := TIME_SUBTRACT(P_Date_Time_In, P_Date_Time_Out);
            End If;
            P_Todays_Absent := 0;
            P_Tardiness := 0;
            P_Tardiness_Late := 0;
         End If;
         -- converting 'Offical Work Hours' and 'Total Work Hours' into Date Format
         If V_Official_Hours >=60 then
            P_Official_Work_Hours := TO_DATE( TO_CHAR(P_Date,'DD-MM-YYYY')||' '||
                                    ( ( V_Official_Hours   - MOD(V_Official_Hours,60) )   / 60 )||':'||
                                    MOD(V_Official_Hours,60),'DD/MM/YYYY HH24:MI');
         Else
            P_Official_Work_Hours := TO_DATE( TO_CHAR(P_Date,'DD-MM-YYYY')
                                    ||' 00:'|| MOD(V_Official_Hours,60),'DD/MM/YYYY HH24:MI');
         End if;
         If V_ATotal_Hours >= 60 Then
            P_Total_Work_Hours    := TO_DATE( TO_CHAR(P_Date,'DD-MM-YYYY')||' '||
                                     ( ( V_ATotal_Hours   - MOD(V_ATotal_Hours,60) )   / 60 )||':'||
                                     MOD(V_ATotal_Hours,60),'DD-MM-YYYY HH24:MI');
         Else
            P_Total_Work_Hours    := TO_DATE( TO_CHAR(P_Date,'DD-MM-YYYY')
                                     ||' 00:'|| MOD(V_ATotal_Hours,60),'DD-MM-YYYY HH24:MI');
         End if;
     -- 18-MAY-2004 to handle the long living error of hours
     Exception when others then P_Total_Work_Hours := Trunc(P_Date); P_Official_Work_Hours := Trunc(P_Date);
     End GET_LATE_TIME;

--this function returns TRUE if P_DATE is THU,FRI, or holiday date, otherwise returns FALSE.
-- By Suhaibani 18-Jun-2006
Function  IS_WEEKEND_HOLIDAY (P_Date IN Date)
 Return Varchar2 IS
V_HOLIDAY Varchar2(6);
Begin
   IF TO_CHAR(P_DATE,'DY') in ('FRI','THU') THEN RETURN 'True';
   END IF;

Exception
     When Others Then V_HOLIDAY:='False';
   End;
   RETURN V_HOLIDAY;
END;

IF (V_START_TIME IS NOT NULL  AND V_END_TIME IS NOT NULL) THEN        --Added for TAMAM

--Added for TAMAM, Starts

ELSE

if ( V_START_DATE < TRUNC(P_DATE) AND V_END_DATE > trunc(P_DATE) ) OR
                   ( V_START_DATE = TRUNC(P_DATE) AND V_END_DATE > trunc(P_DATE))  OR
                   ( V_START_DATE = TRUNC(P_DATE) AND V_END_DATE = trunc(P_DATE) AND P_ABS_REASON_ID IS NULL) OR
                   ( V_START_DATE < TRUNC(P_DATE) AND V_END_DATE = trunc(P_DATE)) THEN
                   Return 1 ;
                elsif ( V_END_DATE = TRUNC(P_DATE) AND  V_START_DATE = TRUNC(P_DATE) AND P_ABS_REASON_ID IS NOT NULL ) THEN
                   return 0.5;
                 else Return 0;
         end if;

END IF;

--Added for TAMAM, Ends

End GET_LEAVE_IN_DAY;

V_Leave_Days:=null;
               P_Leave_type:=null;
               if P_LastRecrod='True' then  -- Calaculate leave only in last record (Need to recheck)- suhaibani
                  FOR L in ALL_LEAVE_IN_DAY  LOOP     -- in case there are more than one leave, then combine them (Suhaibani)
                     if nvl(L.Leave_Sum,0)!=0 then
                        V_Leave_Days:=nvl(V_Leave_Days,0)+ L.Leave_Sum;
                        if P_Leave_Type!=null then P_Leave_type:=P_Leave_Type||' + ' ||L.Leave_type||'('||to_char(nvl(V_Leave_Days,0))||')';
                        else P_Leave_type:=L.Leave_type||'('||to_char(nvl(V_Leave_Days,0))||')';
                        end if;
                     end if;
                  end loop;
               end if;
            Else
               P_Leave_Type := Null;
            End If;
         Else
            P_Leave_Type := Null;
         End If;
     End GET_COUNTER_INFO;
     -- This Procedure Calculate the OverTime, Standby, Hazardous Low and High of the employee
     -- Selet the employee Submit Plans Details
     -- get the Overtime, standby , Hazadious information from submit plan
     -- calclute the actual Ovet time
     -- convert ovetime into time format
     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) IS
     V_Holiday_Date         Date;
     V_OT_Days              Number;
     V_OT_Fraction          Number;
     V_Travel_Hours         Number;
     V_Weekly_Work_Hours    Number;
     V_Weekly_OTR           Number;
     Weekly_leaves          Number; --by hadba, sum of leaves in the week

Cursor Plan_Detail is
     -- Selet the employee Submit Plans Detaails
     Select B.SEGMENT1 Plan_Type, To_Date(SEGMENT2,'DD-MON-YYYY') Period_From ,
            To_Date(SEGMENT3,'DD-MON-YYYY') Period_To, SEGMENT4 Planned_Hours, SEGMENT5 Planned_days
       From PER_PERSON_ANALYSES A, PER_ANALYSIS_CRITERIA  B, FND_ID_FLEX_STRUCTURES C
      Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
        And A.PERSON_ID = P_Person_Id
        And A.ID_FLEX_NUM = C.ID_FLEX_NUM
        And C.ID_FLEX_STRUCTURE_CODE = 'SUBMIT_PLAN'
        And P_Date Between To_Date(B.SEGMENT2,'DD-MON-YYYY') And Nvl(To_Date(B.SEGMENT3,'DD-MON-YYYY'),P_Date)
            AND P_Date Between A.DATE_FROM And nvl(A.DATE_TO,P_Date);

Begin
        -- Select the date as if the current processing date falls in Holiday Calendar

Begin
             Select HOLIDAY_DATE InTo V_Holiday_Date
               From HXT_HOLIDAY_CALENDARS C, HXT_HOLIDAY_DAYS_VL D
              Where C.ID=D.HCL_ID
                And TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(HOLIDAY_DATE ,'DD-MON-YYYY')
                And P_Date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
             Exception
                 When Others Then V_Holiday_Date:=SYSDATE+10000;   --set virtual invalid holiday date By suhaibani 9-1-2007
                 
         End;
         
         Begin
             --its 60 minutes normally in AEC
             -- in holidays 60 minutes over time is given
             SELECT To_Number(RTrim(Ltrim(Nvl(GLOBAL_VALUE,0)))) InTo V_Travel_Hours
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_TRAVEL_HOURS'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                    V_Travel_Hours := 60;
         End;
         -- Calculating the Overtime, standby , Hazadious information from submit plan
         If P_Plan_Flag = 'False' Then
            If P_LastRecord='True' Or To_Char(P_Official_Work_Hours,'HH24:MI') Not Like '00:00' Then
               P_Plan_Flag := 'True';
               FOR plan_detail_rec in plan_detail loop
                   IF plan_detail_rec.plan_type = 'OT' THEN
                      IF NVL(Plan_Detail_Rec.PLANNED_HOURS,0) > 0 THEN
                         -- find the OT_Days by finding the diff between two dates
                         IF NVL(Plan_Detail_Rec.PLANNED_DAYS,0) > 0 THEN
                            V_OT_Days := Plan_Detail_Rec.PLANNED_DAYS;
                         ELSE
                            V_OT_Days := Plan_Detail_Rec.PERIOD_TO - plan_detail_rec.PERIOD_FROM + 1;
                         END IF;
                         V_OT_Fraction := MOD( NVL(Plan_Detail_Rec.PLANNED_HOURS,0),V_OT_Days );
                         IF P_Date = Plan_Detail_Rec.PERIOD_FROM THEN
                            P_OTR := ( (NVL(plan_detail_rec.PLANNED_HOURS,0) - V_OT_Fraction ) / V_OT_Days ) +
                                     V_OT_Fraction;
                         ELSE
                            P_OTR := ( (NVL(plan_detail_rec.PLANNED_HOURS,0) - V_OT_Fraction ) / V_OT_Days );
                         END IF;
                         -- if day is Friday then the over time is Special Over Time
                         IF TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY')
                            OR TO_CHAR(P_Date,'DAY')  IN ('FRIDAY') then
                            P_OTS := P_OTR + 1;
                            P_OTR := Null;
                            P_OTR_Approved := Null;
                         END IF;
                      END IF;
                   -- find Hazard Low
                   ElsIf Plan_Detail_Rec.PLAN_TYPE = 'HZ'  THEN
                      P_Hazard_Days := 1;
                   -- find Hazard High
                   ElsIf Plan_Detail_Rec.PLAN_TYPE = 'HZH'  THEN
                      P_Hazard_Days_Hight := 1;
                   -- find Standby
                   ElsIf Plan_Detail_Rec.PLAN_TYPE = 'SB' THEN
                      P_Stand_By_Days := 1;
                   End If;
               End Loop;
            End if;
         End If;
         -- For Flex Shift Type Overtime is Calculated at Weekend i.e WEDNESDAY only for thr whole week
         If (P_Attendance_Type = 'SF' OR P_Attendance_Type='SR') AND LTRIM(RTRIM(TO_CHAR(P_Date,'DAY'))) Not IN ('THURSDAY','FRIDAY') AND TO_CHAR(P_Date,'DD-MON-YYYY')!=TO_CHAR(V_Holiday_Date,'DD-MON-YYYY') Then --add SR by hadba
            If LTRIM(RTRIM(TO_CHAR(P_Date,'DAY'))) = 'WEDNESDAY' And P_Plan_Flag1 = 'False' Then
               If P_LastRecord='True' Or To_Char(P_Official_Work_Hours,'HH24:MI') Not Like '00:00' Then
                  P_Plan_Flag1 := 'True';
                 begin
                     -- 20 Feb 2007 Query Updated Suhaibani added NVL(0) to solve new employees problem starting on Wed.
                      Select NVL(Sum(apps.HXT_TIME_ATTENDANCE_AEC.CONVERT_TIMETO_NUMBER(To_Number(To_Char(Total_Work_Hours,'HH24.MI')))),0),
                             NVL(Sum(apps.HXT_TIME_ATTENDANCE_AEC.CONVERT_TIMETO_NUMBER(Nvl(REGULAR_OVERTIME_SUGGESTED,0))),0), sum(nvl(COUNTER_LEAVE_DAYS,0)) --by hadba leave days
                        InTo V_Weekly_Work_Hours, V_Weekly_OTR, Weekly_leaves -- by hadba weekly leave
                        From HXT_ATTENDANCE_AEC
                       Where Person_Id = P_Person_Id
                         And Trunc(Date_Time_out) Between P_Date-4 And P_Date-1;  -- changed by suhaibani it was -6 to -1
                                                -- 18May09 changed by suhaibani to Date_Time_out to correct weekly hrs calculations for overtime, it was Date_Time_in
                                                                        
                 end;
                  V_Weekly_Work_Hours := V_Weekly_Work_Hours + CONVERT_TIMETO_NUMBER(To_Number(To_Char(P_Total_Work_Hours,'HH24.MI')));
                  P_OTR := V_Weekly_OTR + CONVERT_TIMETO_NUMBER(Nvl(P_OTR,0));
                 If P_Date_Type = 'Normal' Then --by hadba to add excused hours full day=1 , half day=0.5  into work hours
                    if P_Leave_Type like '%5%' then-- to add wed half day leave
                        Weekly_leaves := Weekly_leaves + 0.5;
                   end if;
                   if P_Leave_Type like '%1%' then-- to add wed full day leave
                        Weekly_leaves := Weekly_leaves + 1;
                   end if;
                 V_Weekly_Work_Hours := V_Weekly_Work_Hours+((Weekly_leaves)*8);-- by hadba to add the rest week's leaves from Sat to Tue
                 V_Weekly_Work_Hours := Convert_NumberTo_Time(V_Weekly_Work_Hours);
                  else
                  V_Weekly_Work_Hours := Convert_NumberTo_Time(V_Weekly_Work_Hours);
                  end if;
                  P_OTR := Convert_NumberTo_Time(P_OTR);
                  P_OTR_Approved := 0;
                  If P_Date_Type = 'Normal' Then                    
                     If V_Weekly_Work_Hours > 40 Then
                        P_OTR_Approved := V_Weekly_Work_Hours - 40;
                     End If;
                  Else
                     If V_Weekly_Work_Hours > 30 Then
                        P_OTR_Approved := V_Weekly_Work_Hours - 30;
                     End If;
                  End If;
               End If;
            End If;
         Else
            -- Calculating the Actual Overtime of the employee
            If P_Total_Work_Hours > P_Official_Work_Hours Then  --And To_Char(P_Official_Work_Hours,'HH24:Mi') Like '00:00'
               If TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY')
                  OR LTRIM(RTRIM(TO_CHAR(P_Date,'DAY')))  IN ('FRIDAY') then
                     --P_OTS_Approved := Nvl(Time_Subtract(P_Official_Work_Hours, P_Total_Work_Hours),0)+60-P_OverTime_Deduct;  changed by suhaibani no need for +60
                     P_OTS_Approved := Nvl(Time_Subtract(P_Official_Work_Hours, P_Total_Work_Hours),0)-P_OverTime_Deduct;
                     P_OTS_Approved := To_Number( ( P_OTS_Approved - MOD(P_OTS_Approved,60) ) / 60||'.'||LPad(MOD(P_OTS_Approved,60),2,0));
               Else
                     P_OTR_Approved := NVL(Time_Subtract(P_Official_Work_Hours, P_Total_Work_Hours),0)-P_OverTime_Deduct;
                     P_OTR_Approved := To_Number( ( P_OTR_Approved - MOD(P_OTR_Approved,60) ) / 60||'.'||LPad(MOD(P_OTR_Approved,60),2,0));
               End If;
            End If; 
         End If;
         -- 30 minutes are deduted from the overtim if it is grater than 5 hours. -- Added hloday condition by suhaibani 9-1-2007, added THU,FRI Check 17-1-2007
         If P_OTR_Approved > 5 and ((P_Attendance_Type NOT LIKE 'SF' OR P_Attendance_Type NOT LIKE 'SR') OR   TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY') OR LTRIM(RTRIM(TO_CHAR(P_Date,'DAY')))  IN ('THURSDAY','FRIDAY')) Then --add SR by hadba
               P_OTR_Approved := Convert_NumberTo_Time(Convert_TimeTo_Number(P_OTR_Approved) - (30/60));
         End If;                                               -- Added hloday condition by suhaibani 9-1-2007, added THU,FRI Check 17-1-2007
         If P_OTS_Approved > 5 and ((P_Attendance_Type NOT LIKE 'SF' OR P_Attendance_Type NOT LIKE 'SR') OR TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY') OR LTRIM(RTRIM(TO_CHAR(P_Date,'DAY')))  IN ('THURSDAY','FRIDAY')) Then --add SR by hadba
               P_OTS_Approved := Convert_NumberTo_Time(Convert_TimeTo_Number(P_OTS_Approved) - (30/60));
         End If;
         -- this block was moved to be after overtime 30 minuts deduction. it was before it 17-1-2007
         If TO_CHAR(P_Date,'DD-MON-YYYY') = TO_CHAR(V_Holiday_Date ,'DD-MON-YYYY') --- Travel 60 min given to emp
            OR LTRIM(RTRIM(TO_CHAR(P_Date,'DAY')))  IN ('THURSDAY','FRIDAY') then
               If P_LastRecord='True' then
                  If P_OTR_Approved > 0 Then
                    P_OTR_Approved := Convert_NumberTo_Time(Convert_TimeTo_Number(P_OTR_Approved) + V_Travel_Hours/60);
                  ElsIf P_OTS_Approved > 0 Then
                    P_OTS_Approved := Convert_NumberTo_Time(Convert_TimeTo_Number(P_OTS_Approved) + V_Travel_Hours/60);
                  End If;
             End If;
         End if; 
     End GET_PLAN_DETAILS;
     -- it Procedure will get the start and end date for the period in which P_Date will Fall
     Procedure TIME_ATTENDANCE_PERIOD (P_Date IN Date, P_Start_Date Out DATE, P_End_Date Out Date) IS
     Begin
         Select To_Date(Substr(D.VALUE,1,10),'YYYY/MM/DD'), to_date(substr(G.VALUE,1,10),'YYYY/MM/DD')
           Into P_Start_Date, P_End_Date
           From PAY_USER_TABLES A,
                PAY_USER_COLUMNS B, PAY_USER_ROWS_F C, PAY_USER_COLUMN_INSTANCES_F D,
                PAY_USER_COLUMNS E, PAY_USER_ROWS_F F, PAY_USER_COLUMN_INSTANCES_F G
          WHERE A.USER_TABLE_ID = B.USER_TABLE_ID
            AND A.USER_TABLE_ID = C.USER_TABLE_ID
            AND B.USER_COLUMN_ID= D.USER_COLUMN_ID
            AND C.USER_ROW_ID   = D.USER_ROW_ID
            AND A.USER_TABLE_ID = E.USER_TABLE_ID
            AND A.USER_TABLE_ID = F.USER_TABLE_ID
            AND E.USER_COLUMN_ID= G.USER_COLUMN_ID
            AND F.USER_ROW_ID   = G.USER_ROW_ID
            AND SYSDATE BETWEEN C.EFFECTIVE_START_DATE   AND C.EFFECTIVE_END_DATE
            AND SYSDATE BETWEEN D.EFFECTIVE_START_DATE   AND D.EFFECTIVE_END_DATE
            AND SYSDATE BETWEEN F.EFFECTIVE_START_DATE   AND F.EFFECTIVE_END_DATE
            AND SYSDATE BETWEEN G.EFFECTIVE_START_DATE   AND G.EFFECTIVE_END_DATE
            AND A.USER_TABLE_NAME = 'HXT Time Attendance Calendar AEC'
            AND B.USER_COLUMN_NAME = E.USER_COLUMN_NAME
            AND RTRIM(LTRIM(C.ROW_LOW_RANGE_OR_NAME)) LIKE 'Start Date'
            AND RTRIM(LTRIM(F.ROW_LOW_RANGE_OR_NAME)) LIKE 'End Date'
            --AND P_Date BETWEEN To_Date(Substr(D.VALUE,1,10),'YYYY/MM/DD') AND To_Date(Substr(G.VALUE,1,10),'YYYY/MM/DD') --by suhaibani bug
            AND to_char(P_date,'YYYY/MM/DD') BETWEEN Substr(D.VALUE,1,10) AND Substr(G.VALUE,1,10)
            and 'S' = APPS.HXT_TIME_ATTENDANCE_AEC.RET_DATE(D.VALUE)
            and 'S' = APPS.HXT_TIME_ATTENDANCE_AEC.RET_DATE(G.VALUE);
          Exception
          When No_Data_Found Then
               P_Start_Date := Null;
               P_End_Date   := Null;
          When Others Then
               P_Start_Date := Null;
               P_End_Date   := Null;
     End TIME_ATTENDANCE_PERIOD;
     -- Time Subtract Function take two inputs as date and return the diff between these date in Minutes Form
     Function TIME_SUBTRACT (P_time1 IN Date, P_time2 IN Date)  Return Number Is
     hr1 number  := TO_CHAR(P_time1,'HH24');
     hr2 number  := TO_CHAR(P_time2,'HH24');
     thr number;
     min1 number := TO_CHAR(P_time1,'MI');
     min2 number := TO_CHAR(P_time2,'MI');
     tmin number;
     Begin
        -- p_time2 is next date or further more etc
        If trunc(P_Time2)-trunc(P_Time1) >= 1 Then
             hr2 := hr2 + 24* ( trunc(P_Time2)-trunc(P_Time1));
        End if;
        If hr1 > hr2 then
           hr2 := hr2 + 24;
        end if;
        if min1 > min2 then
           min2 := min2 + 60;
           hr2 := hr2 - 1;
        end if;
        thr  := hr2  - hr1;
        tmin := min2 - min1;
        return abs( (thr*60)+tmin );
     End TIME_SUBTRACT;
  -- this will update the record if the plan is enterd after the process run for the current day
  -- i.e if the plan is enterd after the date is passed then this will update the employee record
  -- but these plans should fall in current attendance period.
  -- the record which will be update if these are already singed then they will be marded unsigned
  -- by the system and the manager have to update these unsigned records again.
     PROCEDURE SUBMIT_PLAN_CHECK(P_Person_Id In Number, P_Period_Start In Date, P_Period_End In Date) IS
             V_Date_Type Varchar2(20);
             V_Attendance_Type Varchar2(80);
             V_OTR Number;
             V_OTS Number;
             V_OTR_Approved Number;
             V_OTS_Approved Number;
             V_Hazard_Days Number;
             V_HAZARD_DAYS_HIGH Number;
             V_Stand_By_Days Number;
             V_Plan_Flag Varchar2(5) :=  'False';
             V_Plan_Flag1 Varchar2(5) := 'False';
             V_LastRecord Varchar2(5) := 'False';
             V_last_record date;
             V_Temp_Date date := null;
             V_OverTime_Deduct Number := 0;
             V_Leave_Type  Varchar2(80);
     -- selecting those arecords which have no ovetime etc from employee attendance table
     Cursor Late_Submit_Plan is
     Select * From HXT_ATTENDANCE_AEC D
      Where Person_Id = P_Person_Id
        And Trunc(d.Date_Time_Out) Between P_Period_Start and P_Period_End
        And (Nvl(D.REGULAR_OVERTIME_SUGGESTED,0)= 0 Or Nvl(D.SPECIAL_OVERTIME_SUGGESTED,0) = 0 Or
             Nvl(D.HAZARD_DAYS,0) = 0 Or Nvl(D.HAZARD_DAYS_HIGH,0) = 0 Or Nvl(D.STAND_BY_DAYS,0) = 0)
        And d.Person_Id In(
            Select a.Person_Id
              From PER_PERSON_ANALYSES A, PER_ANALYSIS_CRITERIA  B, FND_ID_FLEX_STRUCTURES C
             Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID
               And A.PERSON_ID = d.Person_Id
               AND C.ID_FLEX_NUM=50326 --Added this line to use index and overcome SQL type mismatch ERROR in this query, Suhaibani 2-Nov-2006
               And A.ID_FLEX_NUM = C.ID_FLEX_NUM
               And C.ID_FLEX_STRUCTURE_CODE = 'SUBMIT_PLAN'
               And Trunc(d.Date_Time_Out) Between To_Date(B.SEGMENT2,'DD-MON-YYYY') And Nvl(To_Date(B.SEGMENT3,'DD-MON-YYYY'),Trunc(d.Date_Time_Out))
               AND Trunc(d.Date_Time_Out) Between A.DATE_FROM And nvl(A.DATE_TO,Trunc(d.Date_Time_Out)) )
      Order by d.date_time_Out;

Begin
         For i In Late_Submit_Plan Loop
            V_Date_Type := HXT_TIME_ATTENDANCE_AEC.GET_DATE_TYPE (P_PERSON_ID,Trunc(i.Date_Time_Out));
            Begin
                -- get the attendance type of the employee because it might also be changed
                Select ATTENDANCE_TYPE InTo V_Attendance_Type From PER_PERSON_ANALYSES A, HXT_OTA_INFO_AEC B
                 Where A.ANALYSIS_CRITERIA_ID = B.ANALYSIS_CRITERIA_ID And A.PERSON_ID = P_Person_ID
                   And Trunc(i.Date_Time_Out) Between NVL(A.DATE_FROM,Trunc(i.Date_Time_Out)) AND NVL(A.DATE_TO,Trunc(i.Date_Time_Out));
                Exception When Others Then  V_Attendance_Type := 'SS';
            End;
            Begin
                -- if employee has more than one record in one day then only last record will be updated
                Select max(date_time_Out) Into V_last_record
                  From HXT_ATTENDANCE_AEC a
                 Where a.person_id = i.person_id
                  and  trunc(date_time_out) = trunc(i.date_time_Out);
            End;
            If V_last_record = i.date_time_out Then
               V_LastRecord := 'True';
            Else
               V_LastRecord := 'False';
            End If;
            If V_Temp_Date = Trunc(i.Date_Time_Out) Then
               V_Temp_Date  := Trunc(i.Date_Time_Out);
            else
               V_Temp_Date  := Trunc(i.Date_Time_Out);
               V_Plan_Flag  := 'False';
               V_Plan_Flag1 := 'False';
            End If;
            -- calculate the ovetime , standby days and hazardous low and high
            HXT_TIME_ATTENDANCE_AEC.GET_PLAN_DETAILS (Trunc(i.Date_Time_Out), i.Person_ID, V_Date_Type,
                                                           i.Official_Work_Hours, i.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_Attendance_Type,
                                                           V_LastRecord, V_Plan_Flag, V_Plan_Flag1, V_OverTime_Deduct, V_Leave_Type);
            -- update the table with new paln information
            Update HXT_ATTENDANCE_AEC Set REGULAR_OVERTIME_SUGGESTED = V_OTR, SPECIAL_OVERTIME_SUGGESTED = V_OTS,
                                      HAZARD_DAYS = V_HAZARD_DAYS, HAZARD_DAYS_HIGH = V_HAZARD_DAYS_HIGH,
                                      STAND_BY_DAYS = V_Stand_By_Days, LAST_UPDATED_BY = 3, LAST_UPDATE_DATE = SYSDATE   -- added last updated by suhaibani
            Where Person_Id = i.Person_Id And date_time_out = i.date_Time_out;
            -- unsigne the all records of the employee of that perticulare date
            Update HXT_ATTENDANCE_AEC Set SIGGNED='N' , LAST_UPDATED_BY = 3, LAST_UPDATE_DATE = SYSDATE   -- added last updated by suhaibani
            Where trunc(date_time_out) = Trunc(i.date_Time_out)
                  And Nvl(Siggned,'N') = 'Y' And Organization_Id = i.Organization_Id;
         End Loop i;
     End;

-- This procedure sign all recrods of all the employee which are not signed and are more than 5 days old
     Procedure Signe_Attendance (P_Date In Date) Is
     V_Holiday_Date         Date;
     V_Signed_Days          Number := 0;
     Continus_Leave_Counter Number := 0;
     -- this is main loop will run for the organization id and business gropu id
     Cursor Signe_Records Is
     Select BUSINESS_GROUP_ID, ORGANIZATION_ID From PER_ALL_ASSIGNMENTS_F C
      Where P_Date between C.EFFECTIVE_START_DATE and C.EFFECTIVE_END_DATE
     Group By Business_Group_Id, Organization_Id;
     Begin
         -- global varible for signing limit of days. i.e system will sing those record which are older than the days
         -- entered in the variable
         Begin
             SELECT To_Number(RTrim(Ltrim(Nvl(GLOBAL_VALUE,0)))) InTo V_Signed_Days --V_Signed_Days --its 5 Days normally in AEC
               FROM FF_GLOBALS_F
              WHERE GLOBAL_NAME LIKE 'AEC_SIGNED_DAYS'
                AND P_Date BETWEEN NVL(EFFECTIVE_START_DATE,P_Date) AND NVL(EFFECTIVE_END_DATE,P_Date);
             Exception
             When Others Then
                  V_Signed_Days := 5;
         End;
         -- this loop fetch the no of leave days found HOLIDAY CALANDER in the previous 5 days
         -- if there are any holiday then these will be added in signed day varible. so system will signe
         -- more older record not the latest record due to holiday
         For j in 1..V_Signed_Days Loop
             Loop
                If rtrim(ltrim(TO_CHAR(P_Date-Continus_Leave_Counter ,'DAY')) ) IN ('THURSDAY','FRIDAY') Then
                   Continus_Leave_Counter  := Continus_Leave_Counter  + 1;
                Else
                   Begin
                       Select HOLIDAY_DATE InTo V_Holiday_Date
                         From HXT_HOLIDAY_CALENDARS C, HXT_HOLIDAY_DAYS_VL D
                        Where C.ID=D.HCL_ID
                          And HOLIDAY_DATE = P_Date-Continus_Leave_Counter
                          And P_Date-Continus_Leave_Counter  BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
                       Continus_Leave_Counter  := Continus_Leave_Counter  + 1;
                       Exception
                       When No_Data_Found Then  Exit;
                       when others then Exit;                                --  10-APR-2004
                   End;
                End If;
             End Loop;
             Continus_Leave_Counter  := Continus_Leave_Counter  + 1;
         End Loop;
         -- signing the old recrod which are equl to or less than the signed day variable.
         For k In Signe_Records Loop
           Begin
             Update HXT_ATTENDANCE_AEC Set SIGGNED = 'Y', LAST_UPDATE_DATE = SYSDATE, LAST_UPDATED_BY =3    --change creation_date to last_update date suhaibani
              Where Trunc(Date_Time_In) <= Trunc(P_Date-Continus_Leave_Counter)
                And Business_Group_Id = k.Business_Group_Id
                And Organization_Id   = k.Organization_Id
                And Nvl(SIGGNED,'N') = 'N';
             If SQL%Found Then
                Declare                                                      -- start 10-APR-2004
                    V_Rec_Count number := 0;
                Begin
                   Select Nvl(Count(*),0) into V_Rec_Count
                     From HXT_AUTO_SIGNE_ATTENDANCE_AEC
                    Where BUSINESS_GROUP_ID = k.Business_Group_Id
                      And ORGANIZATION_ID   = k.Organization_Id
                      And trunc(DATE_TIME)  = trunc(P_Date-Continus_Leave_Counter);
                   If V_Rec_Count = 0 Then
                      Begin
                        Insert Into HXT_AUTO_SIGNE_ATTENDANCE_AEC (BUSINESS_GROUP_ID  , ORGANIZATION_ID  , DATE_TIME)
                               Values (k.Business_Group_Id, k.Organization_Id, trunc(P_Date-Continus_Leave_Counter));
                      Exception when others then null;
                      End;
                   End If;
                Exception when others then null;
                End;
             End If;
             Exception when others then null;
           End;                                                                  -- end 10-APR-2004
         End Loop;
     Exception when others then null;
     End Signe_Attendance;

-- This function will convert the number type data into time format i.e hours.minutes for display
    Function CONVERT_NUMBERTO_TIME(P_Counter In Number) Return Number Is
    V_Minutes Number;
    Begin
      V_Minutes := Round(P_Counter*60,0);
      Return To_Number( ( V_Minutes - MOD(V_Minutes,60) ) / 60||'.'||LPad(MOD(V_Minutes,60),2,0) );
    End CONVERT_NUMBERTO_TIME;
    -- This function will convert the date type i.e hours.minutes into number for calculations
    FUNCTION CONVERT_TIMETO_NUMBER(P_C IN NUMBER) RETURN NUMBER IS
    V_Hours Number;
    V_Minutes Number;
    BEGIN
    V_Hours := Trunc(Nvl(P_C,0));
    V_Minutes := Nvl(P_C,0)-Trunc(Nvl(P_C,0));
    V_Hours := Nvl(V_Hours,0) + ((Nvl(V_Minutes,0)*100)/60); -- DONT ROUND THE HOURS
    Return(V_Hours);
    END CONVERT_TIMETO_NUMBER;
    -- This function is used in IS_DATE Quick Formula for validation of the date in user tabel of HXT Time Attendance
    -- HXT Ramadan Calendar
    FUNCTION RET_DATE(P_DATE IN VARCHAR2) RETURN VARCHAR2 IS
    CHK_DATE DATE;
    BEGIN
       Select To_Date(Substr(p_date,1,10),'YYYY/MM/DD') INTO CHK_DATE FROM DUAL;
       return('S');
    EXCEPTION WHEN OTHERS THEN
        return('E');
    END RET_DATE;
   
    -- This procedure to load actions from APACS system using DAtabase link "AEC08"
    PROCEDURE LOAD_ACTION (p_date IN DATE,P_EMP_NO in varchar2 Default NULL) is
       Cursor Act_list IS
              SELECT EMP_NO,DATE_TIME,TERM_NO,ACTION_TYPE
              from daily_actions@apacs
              where trunc(DATE_TIME)=trunc(p_date)
              and (
                  (upper(replace(emp_no,'-','')) = upper(replace(P_EMP_NO,'-','')) and P_EMP_NO is not null)
                   OR
                  (P_EMP_NO is not null and upper(replace(emp_no,'-','')) =(select upper(replace(TEMPORARY_CARD_NO,'-',''))
                                                                           from HXT_TEMPORARY_CARD_ISSU_AEC a ,PER_ALL_PEOPLE_F b
                                                                           where a.person_id=b.person_id and
                                                                                 b.employee_number=P_EMP_NO and
                                                                                 trunc(a.ISSUED_DATE)=trunc(P_DATE) and
                                                                                 P_DATE between b.Effective_Start_Date And b.Effective_End_Date and
                                                                                 rownum=1))
                   OR
                  (emp_no IS NOT Null and P_EMP_NO is NULL)
                  );
       err_msg varchar2(100);
       V_EMP_NO varchar2 (20);
       BEGIN
          delete from hxt_action_bad_aec;
          For Act_list_rec in Act_list loop
          BEGIN
              if substr(Act_list_rec.EMP_NO,1,1) in ('0','1','2','3','4','5','6','7','8','9') then
                  insert into hxt_action_aec(EMP_NO,DATE_TIME,TERM_NO,ACC_TYPE)
                                                              values(Act_list_rec.EMP_NO
                                      ,Act_list_rec.DATE_TIME
                                  ,Act_list_rec.TERM_NO
                                           ,Act_list_rec.ACTION_TYPE);
               else -- TM TR CN CM ...etc
                   begin
                     select distinct a.employee_number into V_EMP_NO
                     from PER_ALL_PEOPLE_F a
                     where P_DATE Between a.Effective_Start_Date And a.Effective_End_Date AND
                           a.current_employee_flag='Y' and
                           upper(replace(a.employee_number,'-',''))=upper(replace(Act_list_rec.EMP_NO,'-',''));

exception
                        when others then
                            V_EMP_NO:=Act_list_rec.EMP_NO;
                   end;

ELSE
       if P_MGR_ID!=P_Sub_MGR_ID then

--dbms_output.put_line(' - ');
         -- First Find IN time in previous date where FLAG = 2
         In_Time := FIND_IN_DATE_YESTERDAY(P_DATE-1, P_EMP_NO);

P_update_yesterday_record:=v_update_yesterday_record;
     End GET_DATE_TIME_IN_OUT;

End HXT_TIME_ATTENDANCE_AEC;
/