---------------------------------------------------------------------------
function get_leave (p_emP_id in number, p_date in date) return boolean IS
mdays number;
BEGIN
  select leave_id into mdays from set_leave_detail
   where emp_id = p_emP_id and p_date between dt_from and dt_to;
   return true;
exception
	when no_data_found then
   return false;
END;

---------------------------------------------------------------------------

---------------------------------------------------------------------------

function get_suspend (p_emP_id in number, p_date in date) return boolean IS
memp_id number;
BEGIN
  select emp_id into memp_id from set_suspend
   where emp_id = p_emP_id and p_date between dt_from and dt_to;
   return true;
exception
	when no_data_found then
   return false;   
END;
---------------------------------------------------------------------------

---------------------------------------------------------------------------
FUNCTION get_week (p_emp_id in number, p_date in date) return boolean IS
mdays date;
BEGIN
  select wof_date into mdays from set_wof
   where emp_id = p_emP_id and p_date = wof_date;
   return true;
exception
	when no_data_found then
	return false;
END;
---------------------------------------------------------------------------

---------------------------------------------------------------------------
PROCEDURE insert_tags (p_emp_id in number, p_dt_attn in date,  p_ord in number) IS
BEGIN
  insert into emp_tags
  (emp_id, dt_attend, tag_id)
  values
  (p_emp_id, p_dt_attn, p_ord);
END;
---------------------------------------------------------------------------

-------------- This Is When-BUTTON-PRESS TRIGGER---------------------

declare
	mtime_in time_attm.time_in%type;
	mtime_out time_attm.time_out%type;
	cnt number := 0;
	V_ID number;
begin
		delete from emp_tags where
	dt_attend = :dt_attendance;
	for pers in 
	(select id from personnel
	where (nvl(date_leave, :DT_ATTENDANCE) >= :DT_ATTENDANCE
		and nvl(date_appt, :DT_ATTENDANCE) <= :DT_ATTENDANCE)
		and id between 84 and 85)
	loop
		V_Id := pers.id;
			set_tags(pers.id, :dt_attendance);
end loop;
exception
	when no_data_found then
	Message('There Is Error In to Find Employees');
	When others then
	Message('proc----' || V_ID || '-' || sqlerrm);
end;


------------------- TABLES --------------------------------------


CREATE TABLE PERSONNEL
(
 ID                                        NOT NULL NUMBER(6),
 NAME                                               VARCHAR2(40),
 F_NAME                                             VARCHAR2(40),
 ADDR                                               VARCHAR2(100),
 EMAIL                                              VARCHAR2(40),
 NTN                                                VARCHAR2(20),
 GROSS_SAL                                          NUMBER,
 DATE_APPT                                          DATE,
 DATE_LEAVE                                         DATE,
 DATE_OF_CONF                                       DATE
 MGR                                                NUMBER(6),
 DOB                                                DATE,
 GENDER                                             CHAR(1),
 DPT_ID                                             NUMBER,
 INT_CHARGE                                         NUMBER,
 FUEL                                               NUMBER
 CELL_BILL                                          NUMBER,
 CAR_RENT                                           NUMBER,
 CMP_ID                                             NUMBER,
 LOGIN_ALLOWED                                      VARCHAR2(5),
 PASSWORD                                           VARCHAR2(10),
 LANDPHONE                                          VARCHAR2(15),
 CELLPHONE                                          VARCHAR2(15),
 FIELD                                              VARCHAR2(1000),
 MARITAL                                            CHAR(1),
 REFRENCE                                           VARCHAR2(40),
 CONT_END_ON                                        DATE,
 ALT_MOB_NO                                         VARCHAR2(15),
 NIC                                                VARCHAR2(20),
 REL_ID                                             NUMBER,
 NATION_ID                                          NUMBER,
 ANVIS_ID                                           NUMBER,
 ID_DESGN                                           NUMBER,
 SUM_SAL                                            NUMBER,
 ACCOUNT_NO                                         VARCHAR2(20),
 BID                                                NUMBER
)



---------------------------------- 2 ------------------------------------------

CREATE TABLE SET_LEAVE_DETAIL
( 
 LEAVE_ID                                           NUMBER,
 EMP_ID                                             NUMBER,
 REMARKS                                            VARCHAR2(200),
 DT_FROM                                            DATE,
 DT_TO                                              DATE
)

---------------------------------- 3 ------------------------------------------

CREATE TABLE SET_LEAVE
(
 LEAVE_ID                                           NUMBER,
 NAME                                               VARCHAR2(30),
 ABRV                                               VARCHAR2(10)
)

---------------------------------- 4 ------------------------------------------

CREATE TABLE SET_SUSPEND
(
 EMP_ID                                             NUMBER,
 REASON                                             VARCHAR2(200),
 DT_FROM                                            DATE,
 DT_TO                                              DATE,
 SUSPEND_DAYS                                       NUMBER
)


---------------------------------- 5 ------------------------------------------

CREATE TABLE SET_TAGS
(
ID                                                 NUMBER,
TAG                                                VARCHAR2(20),
ABRV                                               VARCHAR2(3),
CHK_TAG                                            NUMBER,
ORDER_BY                                           NUMBER,
ABRR                                               VARCHAR2(4)
)

---------------------------------- 6 ------------------------------------------

CREATE TABLE EMP_TAGS
(
 EMP_ID                                             NUMBER,
 DT_ATTEND                                          DATE,
 TAG_ID                                             NUMBER
)

---------------------------------- 7 ------------------------------------------

CREATE TABLE SET_WOF
(
EMP_ID                                             NUMBER,
WOF_DATE                                           DATE,
ADJ_OFF                                            VARCHAR2(15)
)