Home » Developer & Programmer » Forms » when-validate-item (merged 2) (Oracle Forms 10g, windows)
when-validate-item (merged 2) [message #538658] |
Sun, 08 January 2012 05:53 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/473138e82de51f3ba39f891663c30f04?s=64&d=mm&r=g) |
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](images/smiley_icons/icon_cool.gif)
SSTAFF_NAME VARCHAR2(60)
SRECEIVER_ID VARCHAR2(![Cool](images/smiley_icons/icon_cool.gif)
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.
|
|
|
|
|
Re: when-validate-item (merged 2) [message #538686 is a reply to message #538680] |
Mon, 09 January 2012 00:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Sep 08 07:31:20 CDT 2024
|