function sample

In package spec:

    function ckt_next_order_no
        return varchar2;
------------------------------------------------------------
In package body:

function ckt_next_order_no
        return varchar2
    is
        yymm        varchar2(4);
        ordctlno    varchar2(10);
        nextordno   varchar2(80);
        retval      varchar2(256);
        seq         pls_integer;
    begin
        begin
            select to_char(sysdate, 'YYMM') into yymm from dual;

            select ordctl_ord_no
            into ordctlno
            from ordctl
            where ordctl_cd = 'o';

            if (substr(ordctlno, 3, 4) = yymm)
            then
                seq := substr(ordctlno, 7, 4) + 1;
                nextordno := substr(ordctlno, 1, 6) || lpad(seq, 4, '0');
            else
                nextordno :=
                       'o'
                    || substr(to_char(sysdate, 'YYYY'), 1, 1)
                    || substr(to_char(sysdate, 'YYYY'), 3, 2)
                    || to_char(sysdate, 'MM')
                    || '0001';
            end if;

            retval := 'OK:' || nextordno;
        exception
            when others
            then
                retval := 'ERROR:' || sqlerrm;
        end;

        return (retval);
    end;
  
------------------------------------------------------------
 How to call the function:


            SELECT CKT_CREATE_ADD.CKT_NEXT_ORDER_NO retval
            from dual


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