9 Replies Latest reply: Jun 25, 2013 7:35 AM by Sc0tt RSS

    How to give Custom No. instead of Sequence No.

    abidalim

      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_apex

          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

            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_apex

              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

                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_apex

                  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

                    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_apex

                      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

                        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

                          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.