Home » Developer & Programmer » Forms » Regarding form
Regarding form [message #182996] Wed, 19 July 2006 02:39 Go to next message
orafan2003
Messages: 122
Registered: February 2006
Senior Member
Hi,
I am working on a form for which I am attaching a screenshot.
I have some doubts in it. Kindly guide me.
1) The field "FROM" should get the name of the user who logs in. How can I do that?
2) The field PO number is nothing but the order number which has to come up automatically and that should be the
maximum of order number +1 .Here I thought I should use a sequence.
3)Then when I select manu product name, the rate comes up automatically and I wrote the code for it.I only enter
the quantity and calculate "Value" and "Total value". Now here when I select the product 1 or product 2,the fields
quantity, value and total value should be cleared.How can I do this?
4) There are two tables orders and products. The Product name and Rate are from products block. I am basing my
form on the orders table. So after entering all the values, I click on "send" button for which this
is the code.
go_item('Value');
declare
prodid number;
proddesc varchar2(40);
lang varchar2(10);
begin
select manu_product_id into prodid from manu_product where manu_product_name=:manu_product.manu_product_name;
select manu_product_desc into proddesc from manu_product where manu_product_name=:manu_product.manu_product_name;
select manu_lang into lang from manu_product where manu_product_name=:manu_product.manu_product_name;
insert into Manu_Order
(Manu_Order_ID,
Manu_Product_Name,
Manu_Order_RefNo,
Manu_Order_Date,
Manu_Order_Qty,
Manu_Order_DispatchMode,
Manu_Order_General_Terms,
Manu_Order_ModeOfPayments,
Manu_Order_Tax_Amount,
Manu_Order_Total_Amt,
Manu_Product_ID,
Manu_Product_Desc,
Manu_Product_Rate,
Manu_Lang,
Manu_Inventory_Status,
Manu_Status,
Manu_Quality_Status,
Manu_Order_From)
values
(:manu_order.manu_order_id,
:manu_product.manu_product_name,
:manu_order.Manu_Order_RefNo,
:manu_order.Manu_Order_Date,
:manu_order.Manu_Order_Qty,
:manu_order.Manu_Order_DispatchMode,
:manu_order.Manu_Order_General_Terms,
:manu_order.Manu_Order_ModeOfPayments,
:manu_order.Manu_Order_Tax_Amount,
:manu_order.Manu_Order_Total_Amt,
prodid,
proddesc,
:manu_product.Manu_Product_Rate,
Lang,
'In Process',
'Pending',
'Pending',
:manu_order.Manu_Order_From);

commit_form;
end;
But when I click on the button I get the following error.
FRM 40735 WHEN-BUTTON_PRESSED trigger raised unhandled exception ORA 00001

Kindly help me.


Re: Regarding form [message #183002 is a reply to message #182996] Wed, 19 July 2006 02:47 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
point 2) The user does not want any 'holes' in the 'order number' sequence. An Oracle sequence CAN leave 'holes'. You really should only populate this field just before adding it to the database, not as part of the create_record code.

Point 4) Put some 'begin exception; end;' statements around each 'select' statement so that you can 'see' which statement is giving the exception. You probably has a data_no_found or too_many_rows exception on a table.

David
Re: Regarding form [message #183077 is a reply to message #183002] Wed, 19 July 2006 05:30 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

Point1 :
Create a table for users.
CREATE TABLE t
(user_name VARCHAR2(20),
 pass_word VARCHAR2(15),
 cr_date DATE);  --User creation date

Insert INTO t
VALUES ('TEST', 'TEST', sysdate);


Now in your form, create a control block. Create two text items on that control block. user_name and pass_word.
Change the pass_word field property COnceal Data. Change it to "yes".
Create a separate small canvas and window and place these control items on that canvas. Make your control block the first block in your form so that when form open, user navigates to this block and cannot navigate to othe actual form without entering his username and password.

now, create a trigger When-Validate-Iem for user_name field.
DECLARE
	v_userexist VARCHAR2(15);
BEGIN
	SELECT COUNT(*)
	INTO v_userexist
	FROM t
	WHERE user_name = :CONTROL.user_name;
	IF v_userexist > 0 THEN
			NULL;
	ELSE
        --Show your Alert here.
		RAISE Form_Trigger_Failure;
	END IF;
END;

Then Create a trigger When-Validate-Item for pass_word
DECLARE
	v_username VARCHAR2(15);
	v_password VARCHAR2(15);
BEGIN
	SELECT user_name, pass_word
	INTO v_username, v_password
	FROM t
	WHERE user_name = :CONTROL.user_name;
	IF :CONTROL.pass_word = v_password THEN
          :Your_From_field := :CONTROL.USER_NAME;  --This field is the filed in your attached picture
	ELSE
          --Show your Alert here that password is wrong
     		Raise Form_Trigger_failure;
	END IF;
END;

Only after validating the user this way, user can move to the original form and there in your from field, you can show which user is connected at the moment.

A sample screenshot is attached.

For your Point 3:

Are the fields database fields or not?
In your When-List-Changed trigger, you can assign the values to null for whatever fields you want.

regards,
Saadat Ahmad
Re: Regarding form [message #183270 is a reply to message #183077] Thu, 20 July 2006 03:56 Go to previous messageGo to next message
orafan2003
Messages: 122
Registered: February 2006
Senior Member
Hi,
Thank you for ur guidance.
There is this error which is allowing me to proceed further.
When I use commit_form in when-button-pressed of SEND, I have no problem inserting the values from the form.
But there are some values that have to come from the products table too.Like in the form when I select
Product name, there are 4 other values relating to that in the products table that have to be populated
in the orders table like product id,name,language etc.
And also some other values have to be saved in the orders table.When I write the insert statement, I
am getting errors.

when-button-pressed of SEND

--For values entered in the form to be saved in orders table
COMMIT_FORM;

--For all other values
declare
prodid number;
proddesc varchar2(40);
lang varchar2(10);

begin

select manu_product_id,manu_product_desc,manu_lang into prodid,proddesc,lang from manu_product where manu_product_name=:manu_order.manu_product_name;

insert into Manu_Order
(Manu_Product_Name,
Manu_Product_ID,
Manu_Product_Desc,
Manu_Lang,
Manu_Inventory_Status,
Manu_Status,
Manu_Quality_Status)

values
(:manu_order.manu_product_name,
prodid,
proddesc,
lang,
'In Process',
'Pending',
'Pending');

end;
ora 40735 when-button-pressed raised an unhandled exception ora 01400

Re: Regarding form [message #183289 is a reply to message #183270] Thu, 20 July 2006 04:44 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

select manu_product_id,manu_product_desc,manu_lang into prodid,proddesc,lang
  from manu_product where manu_product_name=:manu_order.manu_product_name;

Run this statement in SQL Plus providing the manu_product_name manually and check how many rows are returned?

regards,
Saadat Ahmad

[Updated on: Thu, 27 July 2006 00:55] by Moderator

Report message to a moderator

Re: Regarding form [message #183294 is a reply to message #183289] Thu, 20 July 2006 04:55 Go to previous messageGo to next message
orafan2003
Messages: 122
Registered: February 2006
Senior Member
Hi,
All the 3 values are returned for that product_name. I checked giving MESSAGE too. But none of the values are getting inserted when I am using INSERT. It says violation of UNIQUE constraint. I am not violating it since the table orders has no values and I am trying to insert.But the form values are getting inserted when I say COMMIT_FORM; My goal is not achieved since the datails of products are not getting populated.
Regards,
Suj
Re: Regarding form [message #183316 is a reply to message #183289] Thu, 20 July 2006 06:21 Go to previous messageGo to next message
orafan2003
Messages: 122
Registered: February 2006
Senior Member
Hi
1) I just changed my form....ie based it only on one table ie orders. So it is working fine...but one of the values is not getting saved .That is the total value which is a database item.
2) Also when I change my selection of products, I want the 3 fields to be cleared so that I can enter new values. How can I do that? Kindly guide me.
Re: Regarding form [message #184554 is a reply to message #183316] Thu, 27 July 2006 00:58 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
1) Verify that the definition in the form is set to database item. Also verify that you are not recalculating each time.
2) Either go to the item and do a clear_item, or set each item to 'null' and then do a synchronize.

David
Re: Regarding form [message #184857 is a reply to message #184554] Fri, 28 July 2006 06:10 Go to previous messageGo to next message
orafan2003
Messages: 122
Registered: February 2006
Senior Member
Hi ,
When I click a button on the form, I am getting this error.
FRM-407355 WHEN_BUTTON_PRESSED raised an unhandled exception ORA 105101

ORA-105101-this is synonymous with ORA_JAVA.EXCEPTION_THROWN

In the when-button-pressed, I am writing a code to call the webservices(Before that I imported some Java classes)

my_object ORA_JAVA.jOBJECT ;
my_object := CALLWEBSVC.new() ;
CALLWEBSVC.CallWebService( my_object, 'xmlData' );

What should be done now?
Re: Regarding form [message #186184 is a reply to message #184857] Sun, 06 August 2006 20:47 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Google 'ORA 105101' and you should get a link to 'GroundBlog'. Look in the 'cached' page as the current page of GroundBlog does not contain the error message.

David
Re: Regarding form [message #186283 is a reply to message #186184] Mon, 07 August 2006 05:15 Go to previous message
orafan2003
Messages: 122
Registered: February 2006
Senior Member
Hi David,
Thank you very much for all your replies to my queries.
I am working out the solutions one after the other. They are working!! Thank you.
Previous Topic: Query in forms
Next Topic: How to insert record into multiply row
Goto Forum:
  


Current Time: Tue Dec 06 15:46:44 CST 2016

Total time taken to generate the page: 0.10896 seconds