Home » Developer & Programmer » Forms » Auto Generate Transaction no with (DDMM0001)this format (10g,Developer 6I,Window XP)
icon1.gif  Auto Generate Transaction no with (DDMM0001)this format [message #378690] Wed, 31 December 2008 05:55 Go to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Dear
Members


I need a transaction no in form which automatically generate with this format

MMDD0001 (First Month,then Date,and then Transaction no)


Any Idea


Thanks

Shahzaib Ismail
Re: Auto Generate Transaction no with (DDMM0001)this format [message #378706 is a reply to message #378690] Wed, 31 December 2008 09:47 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Concatenate month (using desired format) with day (using desired format) with transaction number (probably by using LPAD function) into an item whose datatype is CHARACTER.
Re: Auto Generate Transaction no with (DDMM0001)this format [message #378711 is a reply to message #378706] Wed, 31 December 2008 11:51 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
only required how to generate serial no automatically in form

for example i alter emp table and add one column Serial now its show me on form but how to get serial no automatically

Thanks


Shahzaib
Re: Auto Generate Transaction no with (DDMM0001)this format [message #378753 is a reply to message #378711] Thu, 01 January 2009 04:18 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In a form trigger (such as WHEN-CREATE-RECORD or WHEN-NEW-RECORD-INSTANCE or ...), or in a database trigger (such as BEFORE INSERT).
Re: Auto Generate Transaction no with (DDMM0001)this format [message #378769 is a reply to message #378753] Thu, 01 January 2009 08:59 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
Hellwo Littlefoot

i used this for Database and its work correctly but
I don't know what is code of trigger in form



create or replace trigger cut_trig
before insert on cut
for each row
declare
n number;
begin
select count(*) into n
from cut;
:new.transaction_no:=to_char(sysdate,'DDMM')||ltrim(to_char(n+1,'0000'));
end cut_trig;
Re: Auto Generate Transaction no with (DDMM0001)this format [message #378778 is a reply to message #378769] Thu, 01 January 2009 13:01 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Almost exactly the same as a database trigger!
Re: Auto Generate Transaction no with (DDMM0001)this format [message #378785 is a reply to message #378778] Thu, 01 January 2009 16:15 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
@ Little foot


I Try this but its always given me the same number i want +1 for each entry but in this case the number is coming same


for example
1st entry its given me 20100001
2nd entry its given me 20100001


i need in 2nd entry 20100002


Thanks

Shahzaib Ismail
Re: Auto Generate Transaction no with (DDMM0001)this format [message #378879 is a reply to message #378785] Fri, 02 January 2009 04:13 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It is because you are selecting COUNT(*) from a table which is - from a form point of view - the same as it was during the time you inserted the first record into a data block.

How come? Because these changes were NOT committed!

One would tell: so commit after inserting every record! OK, that might be a solution, but - do you really want to do that? What happens in a multi-user environment? Imagine two users filling in the same form (different data, of course). Every one of them selects COUNT(*) which is the same. DDMM is also the same. Finally, when they commit, you'll get DUP-VAL-ON-INDEX error because both of them will have the same key value!

Therefore, using something like "COUNT(*) + 1" or "MAX(value) + 1" is a bad idea in multi-user environment.

What you might do is to use a sequence. It will always return unique values, nonetheless the number of users - every "sequence.nextval" will return a unique number. However: this will most probably result in gaps (DDMMssss, ssss = sequence):
02010001
02010002
02010023
...
If that's OK, it will simplify the job.

Or, yet another idea: as the first part of the key value is not discussable (it is always DDMM), perhaps you could use one of Forms system variables: :SYSTEM.TRIGGER_RECORD. It returns number of a current trigger record so you might LPAD it in order to create unique keys for records you are entering. Though, it would also jeopardize uniqueness (as every other user would use exactly the same :system.trigger_record values).

In my opinion, you should stick to a sequence.
icon1.gif  Re: Auto Generate Transaction no with (DDMM0001)this format [message #378986 is a reply to message #378690] Sat, 03 January 2009 09:43 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
@ Little foot

Thanks for your reply

as you say that i am not save my first record therefor the next record is not coming with next no you are right and when i commit its work

but my problem is not solve yet, I need next auto generate without save my first record

as you told me that use sequence i don't have any idea about it

i use this query but its not work please read and told me where is my mistake

declare
n number;
begin
select count(*) into n
from cut;
:new.transaction_no:=to_char(sysdate,'DDMM')||ltrim(to_char(n+1,'0000'));
end cut_trig;


Thanks for your reply


your reply is very helpful for me


Shahzaib Ismail
Re: Auto Generate Transaction no with (DDMM0001)this format [message #378988 is a reply to message #378690] Sat, 03 January 2009 09:45 Go to previous messageGo to next message
shahzaib_4vip@hotmail.com
Messages: 376
Registered: December 2008
Location: karachi
Senior Member
declare
n number;
begin
select count(*) into n
from cut;
:new.transaction_no:=to_char(sysdate,'DDMMssss,ssss=sequence')||ltrim(to_char(n+1,'0000'));
end cut_trig;


This is the trigger which i am using for sequence with out saving the record i hope you will solve this problem


Thanks


Shahzaib Ismail
Re: Auto Generate Transaction no with (DDMM0001)this format [message #379034 is a reply to message #378988] Sun, 04 January 2009 02:43 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Talking about a sequence, I had in mind a database object.

Basically, you'd select "sequence.nextval" into a variable and concatenate this value with DDMM extracted from SYSDATE.
Previous Topic: Short Cut Key like alt+s to save
Next Topic: How to Maximize only first Block
Goto Forum:
  


Current Time: Sun Dec 04 02:24:53 CST 2016

Total time taken to generate the page: 0.13557 seconds