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...
in your pre-insert trigger of the datablock
select nvl(max(col),0) + 1
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...
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 :-)
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.