CREATE OR REPLACE FUNCTION WEEK_NO (CDATE VARCHAR2) RETURN NUMBER AS
V_DATE    VARCHAR2(08);
FDATE     DATE;
DNUM      NUMBER;
BEGIN
V_DATE:=CDATE;
--
SELECT DECODE(TO_CHAR(TO_DATE(SUBSTR(V_DATE,1,4)||'0101','YYYYMMDD'),'D'),
               1,7,
               2,6,
               3,5,
               4,4,
               5,3,
               6,2,
               7,1)
      + TO_DATE(SUBSTR(V_DATE,1,4)||'0101','YYYYMMDD')
INTO   FDATE
FROM   DUAL;
--
SELECT TO_DATE(V_DATE,'YYYYMMDD') - FDATE
INTO   DNUM
FROM DUAL;
--
IF DNUM<0 THEN
    RETURN 1;
ELSE
    RETURN TRUNC(DNUM/7) +2;
END IF;
END;