CREATE OR REPLACE package HR_ROSTER as
FUNCTION LATE_START(START_TIME VARCHAR2,str varchar2, INV varchar2) RETURN VARCHAR2 ;
FUNCTION LATE_END(START_TIME VARCHAR2) RETURN VARCHAR2 ;
FUNCTION FIRST_HALF_START(START_TIME VARCHAR2) RETURN VARCHAR2 ;
FUNCTION MAK_TWO_HRS(START_TIME VARCHAR2,str varchar2, INV varchar2)RETURN VARCHAR2 ;
FUNCTION SECOND_HALF_START(START_TIME VARCHAR2)RETURN VARCHAR2 ;
FUNCTION EARLY_GO_START(START_TIME VARCHAR2) RETURN VARCHAR2 ;
FUNCTION EARLY_GO_END(START_TIME VARCHAR2,str varchar2, INV varchar2) RETURN VARCHAR2 ;
FUNCTION LATE_END2(START_TIME VARCHAR2 , str varchar2, INV varchar2) RETURN VARCHAR2 ; -- inv = interval
end;
/
CREATE OR REPLACE package body HR_ROSTER as
FUNCTION LATE_START(START_TIME VARCHAR2,str varchar2, INV varchar2)RETURN VARCHAR2 IS
RETVAL VARCHAR2(100);
strr varchar2(4000);
begin
strr := ' SELECT to_char((to_date('''||start_time||''',' || '''hh24:mi:ss'''|| ' ) + interval ''' || str || ''''||INV||'), ' || '''hh24:mi:ss''' || ') FROM DUAL';
DBMS_OUTPUT.PUT_LINE(Strr);
EXECUTE IMMEDIATE strr INTO RETVAL;
--SELECT to_char((to_date(start_time,'hh24:mi:ss') + interval '15' minute),'hh24:mi:ss') INTO RETVAL FROM DUAL;
RETURN RETVAL;
end;
FUNCTION LATE_END(START_TIME VARCHAR2) RETURN VARCHAR2 IS
MAK_TWO_HRS VARCHAR2(100);
RETVAL VARCHAR2(100);
begin
SELECT to_char((to_date(start_time,'hh24:mi:ss') + interval '2' HOUR),'hh24:mi:ss') INTO MAK_TWO_HRS FROM DUAL;
IF MAK_TWO_HRS IS NOT NULL THEN
SELECT to_char((to_date(MAK_TWO_HRS,'hh24:mi:ss') - interval '1' SECOND),'hh24:mi:ss')INTO RETVAL FROM DUAL;
END IF;
RETURN RETVAL;
end;
FUNCTION FIRST_HALF_START(START_TIME VARCHAR2) RETURN VARCHAR2 IS
RETVAL VARCHAR2(100);
BEGIN
SELECT to_char((to_date(start_time,'hh24:mi:ss') + interval '2' HOUR),'hh24:mi:ss') INTO RETVAL FROM DUAL;
RETURN RETVAL;
end;
FUNCTION MAK_TWO_HRS(START_TIME VARCHAR2,str varchar2, INV varchar2) RETURN VARCHAR2 IS
RETVAL VARCHAR2(100);
strr varchar2(4000);
stime varchar2(20);
fval number;
tval number;
BEGIN
/*
strr := ' SELECT to_char((to_date('''||start_time||''',' || '''hh24:mi:ss'''|| ' ) + interval ''' || str || ''''||INV||'), ' || '''hh24:mi:ss''' || ') FROM DUAL';
DBMS_OUTPUT.PUT_LINE(Strr);
EXECUTE IMMEDIATE strr INTO RETVAL;
--SELECT to_char((to_date(start_time,'hh24:mi:ss') + interval '2' HOUR),'hh24:mi:ss') INTO RETVAL FROM DUAL;
RETURN RETVAL;
*/
if trunc(to_number(str)) = to_number(str) then
strr := ' SELECT to_char((to_date('''||start_time||''',' || '''hh24:mi:ss'''|| ' ) + interval ''' || str || ''''||INV||'), ' || '''hh24:mi:ss''' || ') FROM DUAL';
DBMS_OUTPUT.PUT_LINE(Strr);
EXECUTE IMMEDIATE strr INTO retval ;
-- IF MAK_TWO_HRS IS NOT NULL THEN
-- SELECT to_char((to_date(MAK_TWO_HRS,'hh24:mi:ss') - interval '1' SECOND),'hh24:mi:ss')INTO RETVAL FROM DUAL;
--END IF;
else
stime := start_time ;
fval := trunc(to_number(str)) ;
tval := ((str - trunc(to_number(str))) *60 ) ;
strr := ' SELECT to_char((to_date('''||stime||''',' || '''hh24:mi:ss'''|| ' ) + interval ''' || fval || ''''||' HOUR'||'), ' || '''hh24:mi:ss''' || ') FROM DUAL';
DBMS_OUTPUT.PUT_LINE(Strr);
EXECUTE IMMEDIATE strr INTO stime ;
strr := ' SELECT to_char((to_date('''||stime||''',' || '''hh24:mi:ss'''|| ' ) + interval ''' || tval || ''''||' minute'||'), ' || '''hh24:mi:ss''' || ') FROM DUAL';
DBMS_OUTPUT.PUT_LINE(Strr);
EXECUTE IMMEDIATE strr INTO retval ;
-- IF MAK_TWO_HRS IS NOT NULL THEN
-- SELECT to_char((to_date(MAK_TWO_HRS,'hh24:mi:ss') - interval '1' SECOND),'hh24:mi:ss')INTO retval FROM DUAL;
--END IF;
end if;
RETURN RETVAL;
end;
FUNCTION SECOND_HALF_START(START_TIME VARCHAR2)RETURN VARCHAR2 IS
RETVAL VARCHAR2(100);
BEGIN
SELECT to_char((to_date(start_time,'hh24:mi:ss') + interval '4' HOUR),'hh24:mi:ss') INTO RETVAL FROM DUAL;
RETURN RETVAL;
end;
FUNCTION EARLY_GO_START(START_TIME VARCHAR2)RETURN VARCHAR2 IS
RETVAL VARCHAR2(100);
BEGIN
SELECT to_char((to_date(start_time,'hh24:mi:ss') + interval '6' HOUR),'hh24:mi:ss') INTO RETVAL FROM DUAL;
RETURN RETVAL;
end;
FUNCTION EARLY_GO_END(START_TIME VARCHAR2,str varchar2, INV varchar2)RETURN VARCHAR2 IS
RETVAL VARCHAR2(100);
strr varchar2(4000);
begin
strr := ' SELECT to_char((to_date('''||start_time||''',' || '''hh24:mi:ss'''|| ' ) - interval ''' || str || ''''||INV||'), ' || '''hh24:mi:ss''' || ') FROM DUAL';
DBMS_OUTPUT.PUT_LINE(Strr);
EXECUTE IMMEDIATE strr INTO RETVAL;
--SELECT to_char((to_date(start_time,'hh24:mi:ss') - interval '15' minute),'hh24:mi:ss') INTO RETVAL FROM DUAL;
RETURN RETVAL;
end;
FUNCTION LATE_END2(START_TIME VARCHAR2 , str varchar2 , INV varchar2 ) RETURN VARCHAR2 as
stime varchar2(20);
MAK_TWO_HRS VARCHAR2(100);
RETVAL VARCHAR2(100);
strr varchar2(4000);
fval number;
tval number;
begin
if trunc(to_number(str)) = to_number(str) then
strr := ' SELECT to_char((to_date('''||start_time||''',' || '''hh24:mi:ss'''|| ' ) + interval ''' || str || ''''||INV||'), ' || '''hh24:mi:ss''' || ') FROM DUAL';
DBMS_OUTPUT.PUT_LINE(Strr);
EXECUTE IMMEDIATE strr INTO MAK_TWO_HRS ;
IF MAK_TWO_HRS IS NOT NULL THEN
SELECT to_char((to_date(MAK_TWO_HRS,'hh24:mi:ss') - interval '1' SECOND),'hh24:mi:ss')INTO RETVAL FROM DUAL;
END IF;
else
stime := start_time ;
fval := trunc(to_number(str)) ;
tval := ((str - trunc(to_number(str))) *60 ) ;
strr := ' SELECT to_char((to_date('''||stime||''',' || '''hh24:mi:ss'''|| ' ) + interval ''' || fval || ''''||' HOUR'||'), ' || '''hh24:mi:ss''' || ') FROM DUAL';
DBMS_OUTPUT.PUT_LINE(Strr);
EXECUTE IMMEDIATE strr INTO stime ;
strr := ' SELECT to_char((to_date('''||stime||''',' || '''hh24:mi:ss'''|| ' ) + interval ''' || tval || ''''||' minute'||'), ' || '''hh24:mi:ss''' || ') FROM DUAL';
DBMS_OUTPUT.PUT_LINE(Strr);
EXECUTE IMMEDIATE strr INTO MAK_TWO_HRS ;
IF MAK_TWO_HRS IS NOT NULL THEN
SELECT to_char((to_date(MAK_TWO_HRS,'hh24:mi:ss') - interval '1' SECOND),'hh24:mi:ss')INTO retval FROM DUAL;
END IF;
end if;
RETURN RETVAL;
end;
END;
/
