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  |
 |
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 #620151 is a reply to message #620147] |
Tue, 29 July 2014 08:20  |
cookiemonster
Messages: 13963
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 |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
megha2525 wrote on Tue, 29 July 2014 14:01Hi 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 |
|
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 |
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 #620186 is a reply to message #620180] |
Tue, 29 July 2014 15:19 |
|
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 |
 |
Littlefoot
Messages: 21823
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 likeif :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; |
|
|
|
Goto Forum:
Current Time: Sat Feb 08 19:23:50 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
