OraFAQ Forum: Forms » when-validate-item (merged 2)

Home » Developer & Programmer » Forms » when-validate-item (merged 2) (Oracle Forms 10g, windows)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

when-validate-item (merged 2) [message #538658]

Sun, 08 January 2012 05:53

athark
Messages: 4
Registered: January 2012
Location: Delhi

Junior Member

we are dealing in matrial(items) sending & receiving.
i have 3 tables
1)material_master
2)ship_master
3)ship_detail.

material master table holds all the details of matrial(items)
SQL> desc tbs;
Name Null? Type
---------------------------------------------------------------------------------------------------
MATID NOT NULL NUMBER(10)
MATCODE NOT NULL VARCHAR2(15)
DESCRIPTION VARCHAR2(100)
PARTNO VARCHAR2(50)
DESCRIPTION1 VARCHAR2(100)
PARTNO1 VARCHAR2(50)
MANUFACTURER VARCHAR2(50)
CAT2 VARCHAR2(50)
CAT3 VARCHAR2(100)
CAT5 VARCHAR2(50)
CAT6 VARCHAR2(100)
CAT7 VARCHAR2(100)
COMMCODE VARCHAR2(20)
NSMCODE VARCHAR2(20)
UNIT_TYPE_ID NUMBER(10)
PARTSINFIELD VARCHAR2(10)
MTBF NUMBER(10)
MINQTYMTBF VARCHAR2(20)
THREEMONTHRPR VARCHAR2(50)
REMARKS VARCHAR2(100)
MTTR VARCHAR2(10)
CAT1 VARCHAR2(25)
CAT4 VARCHAR2(25)
ABHA_QTY NUMBER(5)
ARAR_QTY NUMBER(5)
BAHA_QTY NUMBER(5)
BURAYDAH_QTY NUMBER(5)
CSTORE_QTY NUMBER(5)
DAMMAM_QTY NUMBER(5)
DAWADMI_QTY NUMBER(5)
HAFARAL_QTY NUMBER(5)
HAIL_QTY NUMBER(5)
HOFUF_QTY NUMBER(5)
JEDDAH_QTY NUMBER(5)
JIZAN_QTY NUMBER(5)
NAJRAN_QTY NUMBER(5)
MAKKAH_QTY NUMBER(5)

sending and receiving items is done thru ship_mstr and ship_detail.

ship_mast

SQL> desc ship_mstr;
Name Null? Type
---------------------------------------------------------------------------------------------------
BILL_ID VARCHAR2(10)
BILL_TYPE VARCHAR2(80)
BILL_DATE DATE
PLACE_FROM VARCHAR2(45)
PLACE_TO VARCHAR2(45)
SITE_NAME VARCHAR2(150)
SSTAFF_ID VARCHAR2( Cool

SSTAFF_NAME VARCHAR2(60)
SRECEIVER_ID VARCHAR2( Cool

SRECEIVER_NAME VARCHAR2(60)
SHIP_BY VARCHAR2(30)
SHIP_REASON VARCHAR2(80)

ship_dtl

SQL> desc ship_dtl;
Name Null? Type
---------------------------------------------------------------------------------------------------
BILL_ID VARCHAR2(10)
MAT_CODE VARCHAR2(15)
MAT_ID NUMBER(10)
SEND_QTY NUMBER(5)
REC_QTY NUMBER(5)
AST_NO VARCHAR2(15)
TC_NO VARCHAR2(15)
TRANSACT_NOTE VARCHAR2(100)

when we enter the sent quantity in ship dtl block,it shud compare the available quantity in masterial_master table
if send quantity is greater than stock quantity an alert shud get generated and if send quantity is less than or equal to
the stock quantity,entered send quantity value must get minus from the material_master table.

i have already tried with when_validate_item trigger on send quantity as shown below

Declare
var_qty number(5);
mcode varchar2(20);

BEGIN

if (:ship_mstr.place_from = 'JEDDAH MC') and (:ship_mstr.bill_type in ('send to other store','send to site','send to staff','send consumed')) then
select matcode,jeddah_qty into mcode,var_qty from tbs
where matcode=:ship_dtl.mat_code;

update tbs tb
set tb.jeddah_qty = nvl(tb.jeddah_qty,0) - nvl(:ship_dtl.send_qty,0)
where tb.matcode=:ship_dtl.mat_code;
forms_ddl('commit');

if var_qty < :ship_dtl.send_qty then
message('Send qty cannot be more than stock quantity.');
raise form_trigger_failure;
end if;

end if;
END;

with this code,the quantity in material_master is not getting effected.

kindly please need help.

Report message to a moderator

Re: when-validate-item (merged 2) [message #538673 is a reply to message #538658]

Sun, 08 January 2012 10:27

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

with this code,the quantity in material_master is not getting effected

I agree. MATERIAL_MASTER table is never used in that piece of code, how would it be affected?

Report message to a moderator

Re: when-validate-item (merged 2) [message #538680 is a reply to message #538673]

Sun, 08 January 2012 23:18

athark
Messages: 4
Registered: January 2012
Location: Delhi

Junior Member

declare

var_qty number(5);
mcode varchar2(15);
begin
if (:ship_mstr.place_from = 'JEDDAH MC') and (:ship_mstr.bill_type in ('send to other store','send to site','send to staff','send consumed')) then
select matcode,jeddah_qty into mcode,var_qty from tbs
where matcode=:ship_dtl.mat_code and matid=:ship_dtl.mat_id;

update tbs tb
set tb.jeddah_qty = nvl(tb.jeddah_qty,0) - nvl(:ship_dtl.send_qty,0)
where tb.matcode=:ship_dtl.mat_code and tb.matid=:ship_dtl.mat_id;
forms_ddl('commit');

if var_qty < :ship_dtl.send_qty then
message('Send qty cannot be more than stock quantity.');
raise form_trigger_failure;
end if;

end if;

end;

Dear Sir,

According to you Material_Master table is never used.you can see above coding material_master indicate to tbs.

Report message to a moderator

Re: when-validate-item (merged 2) [message #538686 is a reply to message #538680]

Mon, 09 January 2012 00:47

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Well, this:
Quote:

i have 3 tables
1)material_master
2)ship_master
3)ship_detail

and this:
Quote:

you can see above coding material_master indicate to tbs.

are rather unrelated.

It is easier to follow what you say if you use actual names, not something you know what it is as you work with it every day. Also, please, before posting your next code snippet, read How to use [code] tags and make your code easier to read?, properly format (indent and such) your code and, finally, use [code] tags which will preserve formatting.

So: you said that TBS table is not updated, right? Two reasons, as far as I can tell:
- IF (ship_mstr.place_from = 'JEDDAH MC') and ... evaluates to FALSE and UPDATE is never executed
- No record in TBS table satisfies UPDATE statement's WHERE clause

As you use Forms 10g, set a break point into that trigger and run a form in debug mode. You'll see what is happening and, hopefully, find a culprit.

From my point of view, strings you use (JEDDAH MC, send to other store, ...) are a magnet for a failure. What if someone uses mixed case? Or puts two spaces between "send" and "to"? Or misspells the word ("sotre" instead of "store")? It just won't work. I'd rather put these values into a separate table and use their codes (numbers, for example - easy to maintain) in a form:

create table bill_type
  (bill_type_id    number,
   bill_type_name  varchar2(50)
  );

insert into bill_type (bill_type_id, bill_type_name) values (1, 'send to other store');
insert into bill_type (bill_type_id, bill_type_name) values (2, 'send to site');
insert into bill_type (bill_type_id, bill_type_name) values (3, 'send to staff');
etc.

In a trigger, it would be

if :ship_mstr.bill_type in (1, 2, 3, 4) then ...

OK, that's just my suggestion, you don't need to follow it.

[Updated on: Mon, 09 January 2012 00:47]

Report message to a moderator

Re: when-validate-item (merged 2) [message #538700 is a reply to message #538686]

Mon, 09 January 2012 01:50

athark
Messages: 4
Registered: January 2012
Location: Delhi

Junior Member

you said that TBS table is not updated, right? Two reasons, as far as I can tell:
- IF (ship_mstr.place_from = 'JEDDAH MC') and ... evaluates to FALSE and UPDATE is never executed
- No record in TBS table satisfies UPDATE statement's WHERE clause

you have catch my problem...in tbs table I already have a column named jeddah_qty and data is also there.
but my problem is when ever i enter send_qty in ship_dtl first it should compare a jeddah_qty in tbs table.if the entered send_qty is less than or equal to jeddah_qty in tbs table,then it must get proccessed and tbs table gets updated.if the send_qty is greater than it must generate a alert message saying 'you dont have enough quantity'.

please kindly help out with details code as my requirement.
will be grateful to you.
DECLARE
CURSOR yourcursor
IS
Select yourcolumn,another_column from yourtable
where some_column between A and B and some_other > sysdate;
begin
for a_record IN yourcursor
loop
do_something_here;
if a_record.another_column = 1 then
do_extra_stuff;
else
do_other_stuff;
end if;
end loop;
end;

just not able to understand this code...for say...some_column,another_column...!!
iam uploading the scree shot of ship_mstr & ship_dtl

Report message to a moderator

Re: when-validate-item (merged 2) [message #538703 is a reply to message #538673]

Mon, 09 January 2012 02:00

athark
Messages: 4
Registered: January 2012
Location: Delhi

Junior Member

if reference to yesterday topic iam posting an image..

Attachment: shipbill.JPG
(Size: 210.39KB, Downloaded 1380 times)

Report message to a moderator

Re: when-validate-item (merged 2) [message #538746 is a reply to message #538703]

Mon, 09 January 2012 04:53

cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) Please read and follow How to use [code] tags and make your code easier to read? as Littlefoot already asked.
2) What has the code in your previous post (message #538700) got to do with the problem?
3) Sounds like you just need a select statement to get the appropriate jeddah_qty and compare it to the value entered.

Report message to a moderator

Previous Topic:	calender
Next Topic:	form dependent on another form...

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 08 07:31:20 CDT 2024