SQL & PL/SQL

Hello All,

I have an issues with my query.
Creating time buckets with slice of 20 min once i give the time in seconds.
But i can't restrict the rows once wit my shift time.I am using a dummy table dba_objects.

select
TO_CHAR(TO_DATE(fn_time(21600),'HH24:MI') + (((rownum-2) * 20)/60/24),'HH24:MI') time_buckets,
bst.shift_num
from
dba_objects,
mtl_parameters mp,
bom_shift_times bst
WHERE mp.ORGANIZATION_ID = 84
AND mp.CALENDAR_CODE=bst.CALENDAR_CODE
AND bst.shift_num in(4,5)
AND rownum <= fn_count_shift(21600,36000)/20 +1

FN_TIME_FUNCTION
CREATE OR REPLACE function APPS.fn_time
(p_date in number) return varchar2
is
v_date varchar2(100);
begin
v_date := p_date/3600;

IF INSTR(v_date,'.')=0 THEN
v_date := v_date;
ELSE
v_date:= SUBSTR(v_date,1,INSTR(v_date,'.')-1)||':'||CEIL((v_date - SUBSTR(v_date,1,INSTR(v_date,'.')-1)) * 60) ;
END IF;
RETURN(v_date);
END fn_time;
/


fn_count_shift function
CREATE OR REPLACE function APPS.fn_count_shift(shift_from NUMBER,shift_to NUMBER) RETURN NUMBER
IS
v_shift_from NUMBER;
v_shift_to NUMBER;
v_no_of_shift NUMBER;
BEGIN
v_shift_from := shift_from/60;
v_shift_to := shift_to/60;

IF v_shift_from <= v_shift_to THEN
v_no_of_shift := v_shift_to - v_shift_from;
ELSE
v_no_of_shift := (24*60) - v_shift_from + (v_shift_to) ;
END IF;

RETURN(v_no_of_shift);
END fn_count_shift;
/

Pls let me know where I am getting wrong.

Thanks
Saurabh

Edited by: SSS2 on Oct 4, 2008 10:54 AM