9 Replies Latest reply: Sep 23, 2013 6:08 AM by Priyasagi RSS

    auto_line_numbering

    shabbir

      Hi Everyone,

       

      I have a query if somebody could please help... I have designed a form. The form is like a header and Lines info thing.

       

      At line level, if user deletes a line it actually gets cancelled and only its visibility is controlled not to show up. Now I need a column of "Line Number". The line number is obviously supposed to be starting from 1 for every new header level thing and the lines cancelled should retain their line numbering i.e. they should not be disturbed in anycase. Can somebody help me how to keep it auto ?

       

      kindly help this.

       

      Regards

      Shabir

        • 1. Re: auto_line_numbering
          Priyasagi

          Hi Shabbir,

           

          Use system.trigger_record to generate serial number.

           

          Example

           

          on when_new_record_instance trigger enter the following statement :srl_no is the field name of your block

          :srl_no:=:system.trigger_record;

          • 2. Re: auto_line_numbering
            shabbir

            Thanks Priyasagi for your reply.

             

             

            I already used :SYSTEM.TRIGGER_RECORD and it worked very well, except when inactive lines are hidden, line numbers gets affected.

             

             

            Actually i have devised the form with an idea that if a line is deleted, it actually gets cancelled and only its visibility is turned off (I did it with a cancelled line flag B.T.W). Now with this functionality when a line is cancelled. the sequence gets affected.

             

             

            Please suggest something more...

             

             

            Regards.

            • 3. Re: auto_line_numbering
              Soofi

              Hi Shabbir,

               

              in your pre-insert trigger of the datablock

              write...

               

              select nvl(max(col),0) + 1

              into col

              from table_name

               

              in the where condition don't check whether it is approved or cancelled.

               

              Provided if you are Deleting the record (not cancelling) then obviously the last maximum value +1 will be generated.

               

              In that case you can keep another column and every time you generate the sequence no update that column with +1 .

               

              So when you delete a record, the next time it will fetch you last updated column value +1

               

              Hope this helps...

               

              Regards,

              Soofi

              • 4. Re: auto_line_numbering
                shabbir

                Thank you Soofi for your feedback,

                 

                 

                Soofi in this case the numbering will only be generated upon saving the entire transaction. the max(col_name) could only get values from DB. I want the line numbering to be generated on "When new line instance"

                 

                 

                i want to do something like ":SYSTEM.TRIGGER_RECORD" should not loose track of lines which are not shown up (due to cancel flag set to "Y").

                 

                 

                Please suggest something more :-)

                 

                 

                Regards

                • 5. Re: auto_line_numbering
                  shabbir

                  can somebody help me in this regard, since i have to meet some timelines for my project.

                   

                  Thanking in anticipation.

                  • 6. Re: auto_line_numbering
                    Priyasagi

                    Hi Shabbir,

                     

                    I think there is no way to go.  I googled for your requirement( DSFR )but no system variable satisfy the requirement.

                    Use system.trigger_record. Allow the enduser to delete record. Write an on-delete-trigger to insert the deleted record to another table.

                    • 7. Re: auto_line_numbering
                      shabbir

                      In ERP application, (Oracle R-12) Requisitions' lines have this functionality. I tried very hard to mimic the functionality but seems like its all in vain

                      • 8. Re: auto_line_numbering
                        Bhushan53

                        You have to do something like -

                         

                        Select  r, col_1, col_2 ...Col_n

                        FROM (Select rownum r, col_1, col_2 ... Col_n from tab_name) base_tab

                        WHERE base_tab.cancelled_flag <> 'Y';

                         

                        Regards

                        Bhushan

                         

                        • 9. Re: auto_line_numbering
                          Priyasagi

                          Hi,

                           

                          Before saving the record, the OP wants to show the serial numbers in the front-end form by using buffer memory.