7 Replies Latest reply: Feb 5, 2013 4:35 AM by G.Y RSS

    create serial number on form level

    MAHA REDDY
      Hi All,

      I am trying to generate the serial no on the form by this coding
      i have taken non data base field SI_NO

      IF :system.record_status IN ( 'NEW' ,'INSERT' ) THEN
      :XXEAM_JOB_TEMPLATE_LINES.SI_NO := :System.CURSOR_RECORD ;
      END IF ;
      If I am deleteting a record from the middle of the record the sequence on the serial number does not gets changed eg. if there r 5 records if I delete 3 record then the sequence of serial number will become 1,2,4,5 where as it should become 1,2,3,4

      same is the issue if I add a record in the middle the serial number gets duplicate eg if there r 5 record and i am adding another record after 2 then its coming like this 1,2,3,3,4,5 where as it should come like this 1,2,3,4,5,6


      Regards,
      Maha.
        • 1. Re: create serial number on form level
          HamidHelal
          MAHA REDDY wrote:
          Hi All,

          I am trying to generate the serial no on the form by this coding
          i have taken non data base field SI_NO

          IF :system.record_status IN ( 'NEW' ,'INSERT' ) THEN
          :XXEAM_JOB_TEMPLATE_LINES.SI_NO := :System.CURSOR_RECORD ;
          END IF ;
          If I am deleteting a record from the middle of the record the sequence on the serial number does not gets changed eg. if there r 5 records if I delete 3 record then the sequence of serial number will become 1,2,4,5 where as it should become 1,2,3,4

          same is the issue if I add a record in the middle the serial number gets duplicate eg if there r 5 record and i am adding another record after 2 then its coming like this 1,2,3,3,4,5 where as it should come like this 1,2,3,4,5,6
          Hi Maha,
          You can use PRE-RECORD trigger at block level and if you write
          :TXT_SL:=:system.trigger_record;
          you will get serial no. After deleting or adding it will not re-organize your serial no un till you navigate at other record. If you navigate through out the record, it will
          re-organized.

          If you want it dynamically, you need to do a loop after any interaction in records.


          Hope understand..

          Hamid
          • 2. Re: create serial number on form level
            G.Y
            Hi Maha
            try this
            trigger When-new-record-instance
            level at= block-level
            <your item> := :system.trigger_record
            this is for new record creating, even if you create your record with in your record it will return correct serial numer.

            likewise if you want to do it for deleting then modify/use it at that trigger.
            but the method is same.
            hope it helps you.
            • 3. Re: create serial number on form level
              MAHA REDDY
              Hi Ghulam Yassen ,

              I have tried when_new_record_instance and pre_record triggers same problem repeating.
              <Block Name.item Name> := :system.trigger_record on block level

              please give me any solution...

              Regards,
              Maha
              • 4. Re: create serial number on form level
                Christian Erlinger
                MAHA REDDY wrote:
                If I am deleteting a record from the middle of the record the sequence on the serial number does not gets changed eg. if there r 5 records if I delete 3 record then the sequence of serial number will become 1,2,4,5 where as it should become 1,2,3,4

                same is the issue if I add a record in the middle the serial number gets duplicate eg if there r 5 record and i am adding another record after 2 then its coming like this 1,2,3,3,4,5 where as it should come like this 1,2,3,4,5,6
                I suggest you should forget about the fact that you can have gaps and the order as this complicates things unnecessary and makes the whole process very unscalable and slow as you'd need to update the primary key of a table for every record when deleting a record with in itself is a bad idea. Also you'd have to lock the entire table which transforms a multiuser application to a single user application. If you need a gapless number for some reason then use rownum in your query. For storing the data use a sequence instead

                http://docs.oracle.com/cd/E11882_01/server.112/e25789/schemaob.htm#CNCPT611

                cheers
                • 5. Re: create serial number on form level
                  MAHA REDDY
                  Hi christian erlinger,

                  How to use rownum in form level i have taken non data base field (SI_NO) ...could you give any idea please.

                  Regards,
                  maha
                  • 6. Re: create serial number on form level
                    Christian Erlinger
                    what is SI_NO? Is it the primary key or part of the primary key or unique in any way? If so then using rownum for this would be a very bad idea.

                    cheers
                    • 7. Re: create serial number on form level
                      G.Y
                      MAHA REDDY wrote:
                      Hi Ghulam Yassen ,

                      I have tried when_new_record_instance and pre_record triggers same problem repeating.
                      <Block Name.item Name> := :system.trigger_record on block level

                      please give me any solution...

                      Regards,
                      Maha
                      Dear Maha as per your question that is the solution which already told you.

                      plz tell what error is showing...
                      or send me ur team ID and paswrd at ghulamyassen@gmail.com

                      thanks.