Scribd
Upload
47 views

Validate Task Flow Chart

This document defines 3 Oracle database functions: 1. CHECK_PLAN checks if an employee is registered under a plan between two dates by querying employee and plan tables. 2. check_Task_out checks if an attendance record exists for an employee on a given date. 3. Check_valid_SHIFT checks if a task date falls within an employee's assigned work shift by querying attendance, work plan, shift, and other tables.

Uploaded by

EngOsamaHelal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
47 views

Validate Task Flow Chart

This document defines 3 Oracle database functions: 1. CHECK_PLAN checks if an employee is registered under a plan between two dates by querying employee and plan tables. 2. check_Task_out checks if an attendance record exists for an employee on a given date. 3. Check_valid_SHIFT checks if a task date falls within an employee's assigned work shift by querying attendance, work plan, shift, and other tables.

Uploaded by

EngOsamaHelal
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

CREATE OR REPLACE FUNCTION SWD_HR.

CHECK_PLAN (V_PERSON_ID IN NUMBER,


V_VAC_END IN DATE) RETURN NUMBER

-- check if the employee is registered under plan at the task date

IS
-- declaring local variables
VALID_VAC NUMBER := 0;
v_date DATE;
CURSOR c1
IS
SELECT PL.PERSON_ID,plans.START_DATE ,plans.END_DATE
FROM swd_hr.swd_emp_work_plans pl , SWD_WORK_PLANS_HR
plans
WHERE PL.PERSON_ID = V_PERSON_ID
and plans.plan_id = pl.plan_id
ORDER BY plans.END_DATE DESC;
BEGIN
-- FUNCTION logic

for i in c1
loop
if trunc(V_VAC_END) between trunc(i.START_DATE) and
trunc(i.END_DATE) then
return 1;
end if;
end loop;

return 0;
EXCEPTION

WHEN OTHERS
THEN
RETURN 0;

END CHECK_PLAN;
/

CREATE OR REPLACE FUNCTION SWD_HR.check_Task_out (V_PERSON_ID IN


NUMBER,v_Task_date IN DATE) RETURN boolean

/* check the task date has an out attendance figerprint */


IS
-- declaring local variables

Valid_out boolean :=false;


V_out_time number:=null;

BEGIN
-- FUNCTION logic
select ATT.OUT_TIME into
V_out_time from SWD_HR.SWD_ATTENDANCE_TRX ATT

where ATT.PERSON_ID=V_PERSON_ID
and ATT.IN_DATE=to_date(v_Task_date);

if V_out_time is null then


return false;
else
return true;
end if;

Exception
when others then
return false;

END check_Task_out;
/

/* Formatted on 31/Jan/18 10:27:11 AM (QP5 v5.277) */


CREATE OR REPLACE FUNCTION SWD_HR.Check_valid_SHIFT (V_PERSON_ID IN
NUMBER,
v_Task_date IN
DATE)
RETURN BOOLEAN
-- this function check if the task not in the workshift of the employee
and it is already in in overtime shift....

IS
-- declaring local variables

Valid_out BOOLEAN := FALSE;


V_in_time NUMBER := NULL;
BEGIN
-- FUNCTION logic

SELECT ATT.IN_TIME
INTO V_in_time
FROM SWD_WORK_PLANS_LINES pl_Lines
INNER JOIN SWD_EMP_WORK_PLANS Emp_PL
ON PL_LINES.PLAN_ID = EMP_PL.PLAN_ID
INNER JOIN SWD_WORK_SHIFTS SH ON PL_LINES.SHIFT_ID =
SH.SHIFT_ID
INNER JOIN SWD_HR.SWD_ATTENDANCE_TRX ATT
ON ATT.PERSON_ID = EMP_PL.PERSON_ID
WHERE 1 = 1
AND ATT.IN_DATE = TRUNC (v_Task_date)
AND ATT.PERSON_ID = V_PERSON_ID
AND TRUNC (v_Task_date) BETWEEN EMP_PL.START_DATE
AND EMP_PL.END_DATE
AND ATT.IN_TIME BETWEEN SH.SHIFT_START AND (SH.SHIFT_STOP -
200); /* if the employee attend his shift and come late unitll before
the shift ends by 2 hours */

IF V_in_time IS NULL
THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
EXCEPTION
WHEN TOO_MANY_ROWS
THEN
RETURN TRUE;
WHEN OTHERS
THEN
RETURN FALSE;
END Check_valid_SHIFT;
/

You might also like