This discussion is archived
9 Replies Latest reply: Sep 23, 2013 4:08 AM by Priyasagi RSS

auto_line_numbering

shabbir Newbie
Currently Being Moderated

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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Explorer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    Hi,

     

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

Legend

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