Home » Developer & Programmer » Forms » data blocks based on the same database table (Oracle forms 10g)
data blocks based on the same database table [message #620103] Mon, 28 July 2014 17:39 Go to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi All,

I am working on a custom built form. There is a button1 on the main canvas.
When a user clicks on button (say button1) - it opens another window1 - the items on this canvas are using the database data block whose source is the database table transactions.
and user will fill in data and close this window1, user is back to main canvas , hits the save button on the menu bar --> a record is inserted to a database table named transactions.

I am working on enhancing this form a little more.
I added a new button (button2) - it opens another window2 - the items on this canvas are using the database data block whose source is the database table transactions.
user will fill in data and close this window2,
user is again back to main canvas, hit the save button on the menu bar -> this time multiple records should be inserted to the same database table transactions.
But, when I press the button2 I get an error message

APP-FND-01206: This record already exists .You entered a duplicate value or sequence of values that must be unique for every record.

My question is should both these data blocks be joined ?

Thanks
Megha

[Updated on: Mon, 28 July 2014 18:20]

Report message to a moderator

Re: data blocks based on the same database table [message #620121 is a reply to message #620103] Tue, 29 July 2014 03:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
My question is what precise code causes that error message to appear?
Re: data blocks based on the same database table [message #620125 is a reply to message #620121] Tue, 29 July 2014 05:21 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you need two windows/canvases? I don't see any improvement in such a design. To me, it looks like tabular form (with, say, 2 records displayed) does the same job, only in an easier manner.

As uniqueness is violated: how do you create unique (or primary) key values? Did you pay attention to assign a different value to records you enter in different windows (i.e. data blocks)?
Re: data blocks based on the same database table [message #620145 is a reply to message #620125] Tue, 29 July 2014 08:01 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi Littlefoot,

Attached is the file that explains the reason for two windows.

Yes - I made sure I am inserting different value records to ensure uniqueness is not violated.

Thanks
Megha



  • Attachment: windows.txt
    (Size: 1.03KB, Downloaded 1228 times)
Re: data blocks based on the same database table [message #620147 is a reply to message #620121] Tue, 29 July 2014 08:05 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi cookiemonster ,

Quote:
My question is what precise code causes that error message to appear?


To answer the above question,

The distribute all button has a
when button pressed trigger
.

There is code in there that is trying to insert multiple records into the table .
However, I made sure I am not inserting duplicate records into the database table but I still get that error.

Thanks
Megha
Re: data blocks based on the same database table [message #620151 is a reply to message #620147] Tue, 29 July 2014 08:20 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
We have wildly different ideas of what the word precise means.
You've got some code that does something and raises an error. We have no idea why you're getting the error because you've told us no useful information. We can't diagnose issues with code we can't see.
Re: data blocks based on the same database table [message #620152 is a reply to message #620145] Tue, 29 July 2014 08:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
megha2525 wrote on Tue, 29 July 2014 14:01
Hi Littlefoot,

Attached is the file that explains the reason for two windows.



Why on earth is that a file attachment? Some people can't/won't download files and that's just some plain text so there's absolutely no reason why you couldn't have pasted it straight in.
Here's the file contents:

Hi Littlefoot,

Here's the reason why there are two windows.
The main canvas is a transactions screen - On the main canvas is a master detail block
Master block has transaction header information and detail block has the transaction detail records (line items).
The master data block is based on the transactions table.
The detail block is based on transaction_items table.
The two buttons are distribute and distribute all .
When the user has to distribute the amount on a single line item - he would click distribute button. Select the account number the amount has to be distributed, hits close and he is back to main canvas.
But if there say 45 line items it is not easy to click the distribute button each time and distribute the amount to an account.
Hence the distribute all button.... here he will select the account number and the amount for all the 45 line items has to be distributed to a single account.
This the reason there are two buttons, two windows and two blocks in the form for distribute button and distribute all button.
Re: data blocks based on the same database table [message #620154 is a reply to message #620151] Tue, 29 July 2014 08:59 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi cookiemonster,

Extremely sorry about that.
This is the code on the distribute_all button (when button pressed trigger)

APP_CUSTOM.OPEN_WINDOW('DISTRIBUTE_ALL');
            go_block('DISTR_ALL');
      go_item('DISTR_ALL.ACCOUNTING_FLEXFIELD');

            
declare

v_dis_id number;
cursor c1 is select * from transaction_items where transaction_id = :trans.transaction_id;
  
begin 
	  
select max(trans_distribution_id)+ 1  into v_dis_id from transaction_distributions; 

for i in c1 
loop

insert into transaction_distributions
            (TRANS_DISTRIBUTION_ID,   
             TRANS_ITEM_ID,            
             GL_ACCT_CCID_NUM,         
             TRANS_DSTBN_DESCRIPTION,
             TRANS_DSTBN_COMMENTS,    
             TRANS_DSTBN_AMOUNT,      
    	    TRANS_DISTRIBUTION_TYPE,  
	    CREATED_BY,              
	    CREATION_DATE,           
	    LAST_UPDATED_BY,         
	    LAST_UPDATE_DATE,         
	 LAST_UPDATE_LOGIN )
values   (				 
           v_dis_id,
           i.trans_item_id,
          :distr_all.gl_acct_ccid_num,
           null,
           null,
           :items.calculated_amount,
           'LA',
           :PARAMETER.CURRENT_USER_ID,
           sysdate,
           :PARAMETER.CURRENT_USER_ID,
          sysdate,
           :PARAMETER.CURRENT_USER_ID);           

end loop;
end;   

[Updated on: Tue, 29 July 2014 09:01]

Report message to a moderator

Re: data blocks based on the same database table [message #620171 is a reply to message #620154] Tue, 29 July 2014 12:28 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Is this used in a multi-user environment? Most likely.
Then it is a poor way to code by getting the max value and adding 1 to it. This will not work in a multi-user environment, which is possibly what is causing your error.

And just what is that "unique sequence of values?"
Re: data blocks based on the same database table [message #620180 is a reply to message #620171] Tue, 29 July 2014 13:35 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi joy_division ,

Thanks you for the reply.
Yes, it is a multi user environment.
The trans_distribution_id is a primary key on the transaction_distributions table.

The initial value on the property palette for this trans_distribution_id is :SEQUENCE.TRANSACTION_DISTRIBUTION_ID_S.NEXTVAL
Please let me know what is the right way to code in this scenario.
Thanks
Megha
Re: data blocks based on the same database table [message #620186 is a reply to message #620180] Tue, 29 July 2014 15:19 Go to previous messageGo to next message
megha2525
Messages: 62
Registered: June 2012
Location: columbus
Member
Hi joy_division ,

I tried this code in the key commit trigger at the form level and it did insert rows into the transaction_distributions table. No errors.
declare


cursor c1 is select * from transaction_items where transaction_id = :trans.transaction_id;
  
begin 



for i in c1 
loop


insert into transaction_distributions
            (TRANS_DISTRIBUTION_ID,   
             TRANS_ITEM_ID,            
             GL_ACCT_CCID_NUM,         
             TRANS_DSTBN_DESCRIPTION,
             TRANS_DSTBN_COMMENTS,    
             TRANS_DSTBN_AMOUNT,      
    				 TRANS_DISTRIBUTION_TYPE,  
						 CREATED_BY,              
						 CREATION_DATE,           
						 LAST_UPDATED_BY,         
						 LAST_UPDATE_DATE,         
						 LAST_UPDATE_LOGIN )
values   (						 
           TRANSACTION_DISTRIBUTION_ID_S.NEXTVAL ,
           i.trans_item_id,
          :distr_all.gl_acct_ccid_num,
           null,
           null,
           i.trans_item_quantity * i.trans_item_unit_price,
           'LA',
           :PARAMETER.CURRENT_USER_ID,
           sysdate,
           :PARAMETER.CURRENT_USER_ID,
           sysdate,
           :PARAMETER.CURRENT_USER_ID);           
commit;
end loop;

end;     
         
APP_STANDARD.EVENT('KEY-COMMIT'); --  this line of code is already there. 


1)But when I hit the save button I get a message saying "FRM-40401 : No changes to save "
But the records are being saved to the database.
Why would forms think that there are no changes to save ?

2)Also, if I hit the distribute_all button again -- the account number is still there .. I just hit close and am back to the main canvas. Now if I hit save , the records are again inserted to the database.
How can I tell forms that there are no changes this time and records should not be inserted to the database.

[Updated on: Tue, 29 July 2014 15:22]

Report message to a moderator

Re: data blocks based on the same database table [message #620188 is a reply to message #620186] Tue, 29 July 2014 15:39 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Now you use a sequence (which is correct), as opposed to "MAX + 1" you used previously (which is wrong); that's why insert suddenly "works".

Forms think that there are no changes because you are inserting records manually (using your own INSERT INTO statement). As far as Forms is concerned, nothing changed in data block so there are no changes to be saved.

You can enter as many records as you want now, because sequence works properly and uniqueness isn't violated any more. If you don't want to insert "duplicates", you'll have to check (i.e. select) the rest of columns and compare them with values you are trying to save - either manually, or by creating a unique index.

On the other hand, you could use IF-THEN-ELSE while putting sequence number into a form item (as you did in your previous message, where you put MAX + 1 into a variable), something like
if :block.sequence_number is null then
   select seq_name.nextval 
     into :block.sequence_number
     from dual;

   insert into ...;
else
   message('Those values are already inserted');
   raise form_trigger_failure;
end if;
Previous Topic: Search form Creating Locks
Next Topic: Interview question on forms ,Steps to add more than one field in the forms
Goto Forum:
  


Current Time: Fri Mar 29 04:05:58 CDT 2024