| insert/update the values into the database tables automatically [message #607568] |
Mon, 10 February 2014 00:04  |
 |
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Hi all,
I have 2 blocks(1database,1control block), i have 1 find button, 5 lov items in the control block canvas,i selected any one of the lov & click on the find button, first navigates to another window & the records displayed at the database block window . So my requirement is i want to isert/update from the navigable window(database block window) & stores the values in the database in the table & click on the save button(main menu in the form)
I don't want to use any buttons. How can i do this please help me.
FIND BUTTON CODE:
DECLARE
finalstr VARCHAR2(5000);
dfinalstr VARCHAR2(5000);
BEGIN
IF :CONTROL_BLOCK.org_code AND :CONTROL_BLOCK.ficsal_year IS NULL THEN
fnd_message.Set_string('Please Select Any Item');
fnd_message.show;
ELSE
finalstr := 'WHERE 1=1';
IF :CONTROL_BLOCK.org_code IS NOT NULL THEN
finalstr := finalstr || 'AND ORGANIZATION_CODE =' ||( :CONTROL_BLOCK.org_code );
END IF;
IF :CONTROL_BLOCK.fiscal_year IS NOT NULL THEN
finalstr := finalstr || 'AND FISCAL_YEAR =' ||( :CONTROL_BLOCK.fiscal_year );
END IF;
IF :CONTROL_BLOCK.item_number IS NOT NULL THEN
finalstr := finalstr || 'AND ITEM_NUMBER =' ||( :CONTROL_BLOCK.item_number );
END IF;
Go_block('DATABASE_BLOCK');
dfinalstr:= Get_block_property('DATABASE_BLOCK', default_where);
Set_block_property('DATABASE_BLOCK', default_where, finalstr);
Execute_query();
END IF;
END;
I want When enter data into form field and click on save button. Automatically the record is inserted into database table
Thank You
[Updated on: Mon, 10 February 2014 01:28] Report message to a moderator |
|
|
|
|
|
| Re: insert/update the values into the database tables automatically [message #607578 is a reply to message #607577] |
Mon, 10 February 2014 02:24  |
|
mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad
|
Senior Member |
|
|
Thank you for reply cookiemonster,
set the properties of insert,update and delete as YES right?.If it is not worked what triggers to use for insert/update the records and click on the save menu option in the forms.where to write(block level or form level?
At Block level triggers
1)WHEN-DATABASE-RECORD
:FORECAST_DATA.last_update_date := sysdate;
Begin
select Fnd_Global.user_id
into :FORECAST_DATA.last_updated_by
from dual;
End;
2)WHEN-CREATE-RECORD
:FORECAST_DATA.creation_date := sysdate;
Begin
select Fnd_Global.user_id
into :FORECAST_DATA.created_by
from dual;
End;
:FORECAST_DATA.last_update_date := :FORECAST_DATA.creation_date;
:FORECAST_DATA.last_updated_by := :FORECAST_DATA.created_by;
The above trigger not effected to automatically insert/update when i press the save menu in the main menu right?
[Updated on: Mon, 10 February 2014 02:27] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: insert/update the values into the database tables automatically [message #607586 is a reply to message #607585] |
Mon, 10 February 2014 02:42 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Quote:I'd set the last_update columns in pre-update (and pre-insert if inserts count for setting them).
I am not getting you point can you please explain more please?[/quote]
I'd put the code to set those items in those triggers rather than the one's you are using. That's what I normally use, but if the triggers you use work feel free to use them.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: insert/update the values into the database tables automatically [message #607615 is a reply to message #607613] |
Mon, 10 February 2014 04:39 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Mon, 10 February 2014 10:35Quote:
1) why you're trying to set them at all.
So, again, what are you actually trying to accomplish?
To insert new record into the table used Set_record_property(v_rec, 'FORECAST_DATA', status, insert_status);
When a user types into a database item in a new record forms automatically changes the status of that record to insert.
So why are you trying to set the status at all?
|
|
|
|
|
|
|
|
| Re: insert/update the values into the database tables automatically [message #607620 is a reply to message #607617] |
Mon, 10 February 2014 04:49 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
No you do not have to write insert_record in an on-insert trigger. You should never nee4d to use either that built-in or that trigger.
Do this - pick a table in your dev DB that you can mess around with the data, one with as few restrictions on what data can be entered as possible - or better yet create a new table with a few columns and no constraints.
Create a new form.
Use the datablock wizard to create a DB block based on that table.
Put all the items on the canvas.
Write no code. None. No triggers, no program units, nothing.
Run the form.
Type some data into the fields and click on save - it'll insert a row.
Execute a query.
Modify some data that's return and click on save - it'll update a row.
Delete a row and click on save - check it's deleted from the DB.
Then ask yourself why you keep insisting that you need to write code to allow users to insert/update data.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
