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.
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.