This discussion is archived
9 Replies Latest reply: Jun 25, 2013 7:35 AM by Sc0tt RSS

How to give Custom No. instead of Sequence No.

abidalim Newbie
Currently Being Moderated

I want to give Custom No. like 13/1 (yy/auto number).........instead of sequence no.??? in Apex 4.2

  • 1. Re: How to give Custom No. instead of Sequence No.
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    hai abidalim ,

     

    you can use the code in Seq # item: if last Seq value as 504 then. The result will be: DOC/00000505/13

    *Default Value

    *Default Value Type

    *PL/SQL function body

    If you want see the demo: http://apex.oracle.com/pls/otn/f?p=19617:2:22281046022493::NO:2::

    user /password: aramani/apex

    ===================================

    Declare   

      Ref_val_ varchar2(16):= Null;

      Seq_Val_ Number;

    begin

        if  :P17_NWM_DOC_ID is not null and  :P17_NWM_DOC_REF_NO is null then

                Select NWM_DOC_ID

               into Seq_Val_

               from DMS_NEW_MASTER where NWM_DOC_ID=:P17_NWM_DOC_ID ;

              select 'DOC'||'/'||lpad(Seq_Val_,8,0)||'/'||to_char(sysdate,'YY')

              into Ref_val_

              from dual;

            -- Populate ref #

          return  Ref_val_;

    elsif :P17_NWM_DOC_REF_NO  is null and  :P17_NWM_DOC_ID is null then

           Select Nvl(count(NWM_DOC_ID),0) + 1

            into Seq_Val_

            from  DMS_NEW_MASTER;

           select 'DOC'||'/'||lpad(Seq_Val_,8,0)||'/'||to_char(sysdate,'YY')

              into Ref_val_

              from dual;

            -- ref number

            return  Ref_val_;

            --summa oru elsif irukku

     

    else

            return Ref_val_;

        end if;

    end;

     

    =============================

    Thanx,

    Ram

  • 2. Re: How to give Custom No. instead of Sequence No.
    abidalim Newbie
    Currently Being Moderated

    Thanks for your reply, but I am not able to do as you did in your demo, I am trying to do with following steps.

    I run the application

    Then Edit Page

    Opened Cust_ID (Primary Key) properties, where I want to use my custom number like '13/auto number( I  want to use MAX(Cust_Code) instead of sequence number.

    In Apex Cust_Code properties, I don't understand where I have to write your procedure. What will be the steps....???

    Thanks

  • 3. Re: How to give Custom No. instead of Sequence No.
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    login in my DEV environment:

     

    workspace: ram_r&d    

    user/password: aramani/apex

    application:19617

     

    you can see the root of producing the seq # with year/month/any text

  • 4. Re: How to give Custom No. instead of Sequence No.
    abidalim Newbie
    Currently Being Moderated

    I created a table Sl_AutoNum(cust_id, name) from scratch  and then as per your instruction I did the same thing, and I got the Auto-number as I want (like 13/2).

    but when I want to save through Apply changes button I got the following error : 

    ORA-01403: no data found.

  • 5. Re: How to give Custom No. instead of Sequence No.
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    hai ,please login my dev environment and see the source code for Reference number generating from MAX seq # (master table seq #)

  • 6. Re: How to give Custom No. instead of Sequence No.
    abidalim Newbie
    Currently Being Moderated

    I checked your dev. environment and I am giving following code in Default value of Cust_ID, but still getting same error

     

    Declare

      Ref_val_ varchar2(10);

    begin

      Select Substr(To_Char(Sysdate, 'YYYY'), 3, 2) || '/' || To_Char(NVL(Max(To_Number(Substr(cust_id, 4, 5))), 0) + 1)

    into Ref_val_

        From TableName

           return Ref_val_;

    end;

  • 7. Re: How to give Custom No. instead of Sequence No.
    Ramani_vadakadu Journeyer
    Currently Being Moderated

    hai your query is wrong? where the  condition of the item?

    like

    if  :P17_NWM_DOC_ID is not null and  :P17_NWM_DOC_REF_NO is null then

     

    http://apex.oracle.com/pls/otn/f?p=19617:2:105760220563626::NO:2::

     

    pls see my code its very clear. at P2_DOC_REF_NO item.

  • 8. Re: How to give Custom No. instead of Sequence No.
    Mike Kutz Expert
    Currently Being Moderated

    Ramni, et al.

    This is a horrible idea!!!

    Especially using COUNT(*)

    I can not tell you how many times this code got my numbers "out-of-wack".

    It will work with ONE person.  But as soon as you add a 2nd user .. BAM!!  You are screwed.

     

    At the very least, use a LOCK TABLE statement and MAX().

    (LOCK TABLE === serialization === slow=true)

     

    For me, we have a sub-section for each year in addition to a human serialized number

    As such we have a 'master table' that looks like this:

    create table master_group_info (
      the_2digit_year number(2) -- for us, this is actually a virtual column based on a DATE column
     ,sub_group_id int
     ,current_max_value int
     ,constraint mgi_pk primary key (the_2digit_year, sub_group_id )
    );

     

    From there, the next sequence value is found like this:

    update master_group_info
      set current_max_value = nvl( current_max_value + 1, 1 )
    where the_2digit_year = l_2digit_year
      and sub_group_id = l_sub_group_id
    returning current_max_value into l_sequence_number_to_use;

     

    However, all of that is within a procedure within a package.

    As such, my APEX Process gets simplified to

    -- code_schema.app_api.add_new_doc( p_doc_id out varchar2, p_filename in varchar2, other_stuff in varchar2 )
    code_schema.app_api.add_new_doc( :P26_DOC_ID, :P26_FILENAME, :P26_OTHER_STUFF );
  • 9. Re: How to give Custom No. instead of Sequence No.
    Sc0tt Expert
    Currently Being Moderated

    Second this.  Trying to come up with unique numbers is not scalable because you need to serialize access to it.  Bad bad idea.

    An alternative approach can be to put a view on top of your table and use analytical functions to pseudo-generate sequences.  You would continue to use a database sequence, but your view can concatenate whatever you want.

    Ex:

    create or replace view my_view as
    select to_char(date_column, 'yy') || '-'||sequence_column
      from your table

    Then have everyone select off of the view.  You can even get fancy by doing stuff like this:

     

    create or replace view my_view as
    select company, date_column,
              company||'-'||to_char(date_column, 'yy')||'-'||row_number() over(partition by company_id, to_char(date_column, 'yy') order by company_id, date_column)
      from your_table

     

    This would basically create sequences by company/year and would reset itself for each company/year.  Obviously I don't know your specific requirements, but you get the idea.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points