OraFAQ Forum: Forms » purchase order numbers

Home » Developer & Programmer » Forms » purchase order numbers (Windows XP Oracle Forms 6i,)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

purchase order numbers [message #316671]

Sun, 27 April 2008 08:58

*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

Report message to a moderator

Re: purchase order numbers [message #316979 is a reply to message #316671]

Tue, 29 April 2008 01:22

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: purchase order numbers [message #317013 is a reply to message #316671]

Tue, 29 April 2008 03:18

*munnabhai*
Messages: 157
Registered: March 2008
Location: Riyadh

Senior Member

Thanks DJ.....

Report message to a moderator

Re: purchase order numbers [message #317024 is a reply to message #316979]

Tue, 29 April 2008 04:04

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: purchase order numbers [message #317186 is a reply to message #317024]

Tue, 29 April 2008 20:29

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: purchase order numbers [message #317204 is a reply to message #317186]

Wed, 30 April 2008 00:07

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: purchase order numbers [message #317376 is a reply to message #317204]

Wed, 30 April 2008 20:31

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 03 04:52:09 CST 2025