Use system.trigger_record to generate serial number.
on when_new_record_instance trigger enter the following statement :srl_no is the field name of your block
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 :-)
can somebody help me in this regard, since i have to meet some timelines for my project.
Thanking in anticipation.
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.
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
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';
Before saving the record, the OP wants to show the serial numbers in the front-end form by using buffer memory.