-------------------------------------------------------------------------------------------------
--------------------------------------------EMPLOYEE ID -----------------------------------------
-------------------------------------------------------------------------------------------------
create or replace function EMPID
return number
is
max_id_for_this_year    number;
this_year               number;
max_year_in             number;
begin
----------------------------------------------------------
select to_char(sysdate,'yyyy')into this_year from dual;
----------------------------------------------------------
select max(employee.emp_no)into max_id_for_this_year
from employee
where substr(employee.emp_no,1,4)=this_year;
----------------------------------------------------------
select max(substr(employee.emp_no,1,4))into max_year_in from employee;
----------------------------------------------------------
--this_year =2006
--max_id_for_this_year=20060010
--max_year_in=2006
----------------------------------------------------------
if max_year_in is null then
   max_id_for_this_year:=to_number(this_year||'0000');
   max_id_for_this_year:=max_id_for_this_year+1;
   return(to_number(max_id_for_this_year));
end if;
----------------------------------------------------------
if max_id_for_this_year is null then
    max_id_for_this_year:=to_number(this_year||'0000');
    if this_year=max_year_in then
      max_id_for_this_year:=max_id_for_this_year+1;
      return(to_number(max_id_for_this_year));
   end if;
end if;
----------------------------------------------------------
if(this_year>=max_year_in and
to_number(substr(max_id_for_this_year,5,4))<('9999'))then
max_id_for_this_year:=max_id_for_this_year+1;
return(to_number(max_id_for_this_year));
else
return null;
end if;
end;
/

-------------------------------------------------------------------------------------------------
--------------------------------------------CUSTOMER ID -----------------------------------------
-------------------------------------------------------------------------------------------------
create or replace function cusID
return number
is
max_id_for_this_year    number;
this_year               number;
max_year_in             number;
begin
----------------------------------------------------------
select to_char(sysdate,'yyyy')into this_year from dual;
----------------------------------------------------------
select max(customer.cus_no)into max_id_for_this_year
from customer
where substr(customer.cus_no,2,4)=this_year;
----------------------------------------------------------
select max(substr(customer.cus_no,2,4))into max_year_in from customer;
----------------------------------------------------------
--this_year =2006
--max_id_for_this_year=20060010
--max_year_in=2006
----------------------------------------------------------
if max_year_in is null then
   max_id_for_this_year:=to_number('1'||this_year||'0000');
   max_id_for_this_year:=max_id_for_this_year+1;
   return(to_number(max_id_for_this_year));
end if;
----------------------------------------------------------
if max_id_for_this_year is null then
    max_id_for_this_year:=to_number('1'||this_year||'0000');
    if this_year=max_year_in then
      max_id_for_this_year:=max_id_for_this_year+1;
      return(to_number(max_id_for_this_year));
   end if;
end if;
----------------------------------------------------------
if(this_year>=max_year_in and
to_number(substr(max_id_for_this_year,6,4))<('9999'))then
max_id_for_this_year:=max_id_for_this_year+1;
return(to_number(max_id_for_this_year));
else
return null;
end if;
end;
/

-------------------------------------------------------------------------------------------------
--------------------------------------------EMPLOYEE AGE-----------------------------------------
-------------------------------------------------------------------------------------------------
Create Or Replace Function Age_Employee_No (X Number)   Return Number
Is
I  Date;
Begin
Select  E.Emp_Birth.Dob  Into I 
From  Employee E
Where Emp_No=X;
Return (Sysdate-I)/365;
end;
/

-------------------------------------------------------------------------------------------------
--------------------------------------------CUSTOMER AGE-----------------------------------------
-------------------------------------------------------------------------------------------------
Create Or Replace Function Age_CUSTOMER_No (X Number)   Return Number
Is
I  Date;
Begin
Select  c.cus_Birth.Dob  Into I 
From  customer C
Where cus_No=X;
Return (Sysdate-I)/365;
end;
/

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