Home » Developer & Programmer » Forms » How to avoid duplicate numbers in post_ (Oracle forms 10gR2)
How to avoid duplicate numbers in post_ [message #362112] Mon, 01 December 2008 00:33 Go to next message
rahshar
Messages: 26
Registered: July 2006
Location: Oman
Junior Member
We have a situation in one of our applications.

we are using oralce forms 10gR2 for development and oracle 10gR2 apps server

We have a passanger booking section for local transport of passengers movement in the organization.

1. The booking section is perfect.
2. the data stored in booking is
Transport no,
serial_no,
Passanger_id
status
Age
Chkd_num

3. when need to check in a passanger as he/she arrives a chkd_num is generated using max + 1 when we enter the save button
a pre-update trigger is fired on commit_form in the save button.

the Screen shot is available as checkin.jpg

and the code is available as checkin.txt

4. for a single terminal the chkd_num generated is fine (max + 1)

5. when we use more than one terminal then we have some of these problems for the chkd_num

a. the number is duplicated such as terminal 1 = 23
terminal 2 = 23

b. some times the number skips 25 then 27

6. We cannot have unique key because the initial value for chkd_num is 0 or null and it should start with 1 for each transport no.

How do these type of situation work, any one come across such situations and any solutions.

----------------
Code for SAVE button
--------------------
DECLARE
CURR_REC NUMBER := 0 ;
BEGIN
:system.MESSAGE_LEVEL := '25' ;
COMMIT_FORM ;
clear_message;

CURR_REC := :booking.CHKD_NUM ;

GO_BLOCK ('booking') ;
CLEAR_BLOCK ;
clear_message;
EXECUTE_QUERY (NO_VALIDATE) ;

GO_RECORD (CURR_REC) ;
:system.message_level := '0';
END ;
=========================================
CODE for PRE-UPDATE TRIGGER (BLOCK_LEVEL)
-----------------------------------------
DECLARE
SEQ NUMBER (3) := 0 ;
BEGIN
:system.MESSAGE_LEVEL := 5 ;
SEQ := 0 ;
-- increase the chkd_num if it is null or 0 when status
-- is changed from confirm to Checked - IN
-------------------------------------------------------
IF :booking.CHKD_NUM = 0 AND :booking.STATUS = 'I' THEN
SELECT max(NVL(chkd_num,0)) + 1
INTO seq
FROM booking
WHERE flight_code = :flights.flight_code;
:booking.CHKD_NUM := SEQ ;
:booking.STATUS := 'I' ;
ELSIF :booking.CHKD_NUM IS NULL
AND :booking.STATUS = 'I' THEN
SELECT max(NVL(chkd_num,0)) + 1
INTO seq
FROM booking
WHERE flight_code = :flights.flight_code;
:booking.CHKD_NUM := SEQ ;
:booking.STATUS := 'I' ;
ELSIF :booking.CHKD_NUM > 0 AND :booking.STATUS = 'I' THEN
:booking.STATUS := 'I' ;
END IF ;
:system.MESSAGE_LEVEL := 0 ;
END ;

files : checkin.jpg screen shot for checkin
for reference
/forum/fa/5377/0/
  • Attachment: checkin.JPG
    (Size: 78.47KB, Downloaded 1038 times)
Re: How to avoid duplicate numbers in post_ [message #362554 is a reply to message #362112] Wed, 03 December 2008 00:28 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I suggest placing this code in the pre-update trigger at the database level. Only one commit happens in an Oracle database at any one time.

David
Previous Topic: Internet Explorer crashes when running a form
Next Topic: Displaying Records data as text grid in oracle forms
Goto Forum:
  


Current Time: Sat Feb 08 07:55:26 CST 2025