This function when executed yeilds the results correctly as per our rqmnt. Only thro procedure its giving a problem with Nulls.
create or replace function CALCULATE_WEEK
(P_DATE IN BIN_BOT.CREATE_DATE%TYPE
)
RETURN NUMBER
IS
last_day_in_the_week BIN_BOT.CREATE_DATE%TYPE;
last_day_in_the_first_week BIN_BOT.CREATE_DATE%TYPE;
number_of_weeks_in_between number;
begin
last_day_in_the_week := next_day(p_date-1,'saturday');
last_day_in_the_first_week := next_day(trunc(last_day_in_the_week,'yyyy')-1,'saturday');
number_of_weeks_in_between := ( to_number(to_char(last_day_in_the_week,'ddd'))
-
to_number(to_char(last_day_in_the_first_week,'ddd'))) / 7;
return ( to_number( to_char(last_day_in_the_first_week,'yyyy') ) * 100
+ number_of_weeks_in_between + 1 );
end;

Thanks