Home » Developer & Programmer » Forms » purchase order numbers (Windows XP Oracle Forms 6i,)
purchase order numbers [message #316671] Sun, 27 April 2008 08:58 Go to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Hi Guyz,

i have some critical query, i dont no how to do this task.

example if i have below sequence numbers

80001
80002
80003

suppose if i issue a purchase order 80001 to company AAA the next purchase order will be 80002

my company requirement is

if again purchase order issued to the same company AAA the sequence number will be continues the changes in the sequence will be 8000X_02 the changes in this sequence is ( _02 )like this if there is any previous purchase order issued to company BBB and wanna to issue new purchase order the sequence will be changed to 8000X_02 if issued again to the same company the sequence number will be continue 8000X_03

if new purchase order issued to the CCC the sequence will be like this 8000X

shall i create sequence everytime on a new vendor ?

how can I create the sequence like this?

Your answer will be highly appreciated.

Regards




Re: purchase order numbers [message #316979 is a reply to message #316671] Tue, 29 April 2008 01:22 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
No. Break the sequence number into its components and use the 'select max(seq_num)+1 from my_table where company = :blk.co_name;' method to get the next 'seq_num'.

David
Re: purchase order numbers [message #317013 is a reply to message #316671] Tue, 29 April 2008 03:18 Go to previous messageGo to next message
*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh
Senior Member
Thanks DJ.....
Re: purchase order numbers [message #317024 is a reply to message #316979] Tue, 29 April 2008 04:04 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Generally speaking,
SELECT MAX(seq_num) + 1
might cause problems in multi-user environment (because more than one user might select the same value, which means that two (or more) records will have the same seq_num (and I guess you don't want to do that)).

Therefore, perhaps you could consider use of a SEQUENCE.
Re: purchase order numbers [message #317186 is a reply to message #317024] Tue, 29 April 2008 20:29 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
@all - The 'max' stuff is done in the 'insert' trigger at the DATABASE level.

@LF - Have a quick reread of first post, the OP wants a sequence number PER COMPANY.

@all but LF - Only ONE 'insert' trigger at the DATABASE level is EVER run at any one time for a particular table.


David
Re: purchase order numbers [message #317204 is a reply to message #317186] Wed, 30 April 2008 00:07 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, that's something completely different; I was assuming (and my assumption was wrong) that you are talking about a form (not a database level) trigger.
Re: purchase order numbers [message #317376 is a reply to message #317204] Wed, 30 April 2008 20:31 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
My 'omit'. I did not specifiy either the trigger or the location in which to do the 'max+1' stuff in my initial reply.

My bad. I should have said ':new', not ':blk'.

David
Previous Topic: unable to delete master which has detail
Next Topic: menu in oracle
Goto Forum:
  


Current Time: Thu Dec 08 02:03:58 CST 2016

Total time taken to generate the page: 0.15982 seconds