텍스트 비교

두 텍스트 파일의 차이점을 찾아보세요

실시간 편집

변경 없는 행 숨기기

줄바꿈 비활성화

레이아웃

비교 단위

구문 강조

Diffchecker Desktop 가장 안전하게 Diffchecker를 사용하는 방법. 데스크톱 앱을 사용하면 비교 데이터가 외부로 전송되지 않습니다!데스크톱 앱 받기

Untitled diff

생성일 11년 전비교 결과 만료 없음

70 삭제

행
총
삭제

글자
총
삭제

이 기능을 계속 사용하려면 업그레이드해 주세요 Diffchecker Pro 요금제 보기

376 행

85 추가

행
총
추가

글자
총
추가

이 기능을 계속 사용하려면 업그레이드해 주세요 Diffchecker Pro 요금제 보기

388 행

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

저장된 비교 결과

원본

파일 열기

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;

수정본

파일 열기

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