OraFAQ Forum: Forms » Receiving Form

Home » Developer & Programmer » Forms » Receiving Form (oracle forms 10g)

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

Receiving Form [message #551908]

Sun, 22 April 2012 05:09

furqan
Messages: 115
Registered: January 2012
Location: Hyderabad

Senior Member

hi all

i have master-detail form which i use to send and receive material.
sending is working fine.in between send n receive i have a pending form which displays records where receive has not been done.

when i double click on bill_id in pending form, receiving from is called and
all master item are appearing through globals in master and iam just entering
receiver id and name and executing query which displays all the send items in detail form with rec_qty blank.enter rec_qty and save.in detail table only rec_qty is getting updated..Good..but when user receive quantity less than send qty is problem.i want to update rec_qty next time when use receive remaining qty with + with previous rec_qty in detail table.

i tried update using

declare
v_count4 number;
begin
select count(*) into v_count4 from ship_dtl
where bill_id=:ship_mstr.bill_id and
mat_code=:ship_dtl.mat_code and
send_qty is not null;
if v_count4 = 0 then

update ship_dtl set
rec_qty=nvl(rec_qty,0)+nvl(:ship_dtl.rec_qty,0);
end if;
end;

on rec_qty(when validate item)its not working
help..!!!

[Updated on: Sun, 22 April 2012 07:52]

Report message to a moderator

Re: Receiving Form [message #551916 is a reply to message #551908]

Sun, 22 April 2012 09:54

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

Senior Member
Account Moderator

What does "it is not working" mean? Any error? If so, which one?

Though, UPDATE statement you posted updates all records in SHIP_DTL table so ... maybe you'll want to add a WHERE clause.

Report message to a moderator

Re: Receiving Form [message #551961 is a reply to message #551916]

Sun, 22 April 2012 23:18

furqan
Messages: 115
Registered: January 2012
Location: Hyderabad

Senior Member

hi..its showing no errors..but its taking the value which iam entering in rec_qty.when i had to enter the value in rec_qty second time its not adding the new value with old value.suppose i send 4 items to a user.he receive just 2 first time,he will enter 2 and save it.after a day he receive another 2,so again he will enter 2 and save it.so rec_qty on that particular record should become 4.but its not doing so.its overwriting the old value with new value..with summing it up.

thanks.

Report message to a moderator

Re: Receiving Form [message #551970 is a reply to message #551961]

Mon, 23 April 2012 01:01

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

Senior Member
Account Moderator

Well, maybe you'd rather use BEFORE UPDATE database trigger. Something like

create or replace trigger bu_qty
  before update on your_table
  for each row
begin
  :new.rec_qty := :old.rec_qty + :new.rec_qty;
end;

Report message to a moderator

Re: Receiving Form [message #552018 is a reply to message #551970]

Mon, 23 April 2012 03:41

furqan
Messages: 115
Registered: January 2012
Location: Hyderabad

Senior Member

create or replace trigger bu_qty
before update on ship_dtl
for each row
begin
:new.rec_qty := :old.rec_qty + :new.rec_qty;
end;

i created trigger as said.but now rec_qty is not getting inserted in table.when i check table rec_qty is showing blank.i tried to update rec_qty through query,it saying 1 row updated but then also is showing blank.

thanks

[Updated on: Mon, 23 April 2012 03:45]

Report message to a moderator

Re: Receiving Form [message #552024 is a reply to message #552018]

Mon, 23 April 2012 04:14

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

Senior Member
Account Moderator

As far as I understood, INSERT is done through a form, when you enter some value in a detail block. Is that not so?

A database trigger has nothing to do with it as it fires only when a record is UPDATED, not inserted, assuming that "old" value is not null. You might try to add a NVL function, though.

Report message to a moderator

Re: Receiving Form [message #552031 is a reply to message #552024]

Mon, 23 April 2012 04:33

furqan
Messages: 115
Registered: January 2012
Location: Hyderabad

Senior Member

it is exactly like that.insertion is done through same form.
before this trigger it was inserting but was not updating.

i just drop the trigger it inserting properly.

[Updated on: Mon, 23 April 2012 04:38]

Report message to a moderator

Re: Receiving Form [message #552033 is a reply to message #552024]

Mon, 23 April 2012 04:43

furqan
Messages: 115
Registered: January 2012
Location: Hyderabad

Senior Member

i write this on WHEN-VALIDATE-ITEM trigger on rec_qty.

update SHIP_DTL
set rec_qty=nvl(rec_qty,0)+nvl(:ship_dtl.rec_qty,0)
where bill_id=:ship_dtl.bill_id
and MAT_CODE=:ship_dtl.mat_code;

no effect ..no errors..still its overwriting the old rec_qty with new rec_qty.not doing sum.

thanks.

Report message to a moderator

Re: Receiving Form [message #552052 is a reply to message #552024]

Mon, 23 April 2012 05:59

furqan
Messages: 115
Registered: January 2012
Location: Hyderabad

Senior Member

waiting for your reply mr.lie to me...!!!

Report message to a moderator

Re: Receiving Form [message #552066 is a reply to message #552052]

Mon, 23 April 2012 07:09

furqan
Messages: 115
Registered: January 2012
Location: Hyderabad

Senior Member

please anybody help me..!!!

Report message to a moderator

Re: Receiving Form [message #552209 is a reply to message #552066]

Tue, 24 April 2012 06:11

chandan.rattan
Messages: 84
Registered: December 2008
Location: India

Member

Hi Furgan.....See as what i have understood from above....I think u need to insert the records which user is receiving other day. I mean as per u "after a day he receive another 2,so again he will enter 2 and save it.so rec_qty on that particular record should become 4.but its not doing so.its overwriting the old value with new value..with summing it up."

U might be updating with existing records, then only it must be happening.I think u need to insert the other 2 records. If u need more help, You can PM me.

Report message to a moderator

Re: Receiving Form [message #552216 is a reply to message #552066]

Tue, 24 April 2012 06:30

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

Senior Member
Account Moderator

@furqan, I don't know what you did in order to NOT make it work properly.

I have just created a test-case (took less than 5 minutes) and everything seems to work just fine.

A table and a database trigger:

create table test
(id      number,
 rec_qty number
);

create or replace trigger bu_qty
  before update on test
  for each row
begin
  :new.rec_qty := :old.rec_qty + :new.rec_qty;
end;

A form is created with a data block wizard (simple 10-records tabular form) - no additional programming.

Check this screenshot-slideshow (created with PrintScreen & MS Paint):

/forum/fa/10093/0/

enter one record and save it
execute query; move to the second record and insert ID 2 values
execute query; enter 3 (i.e. OVERWRITE value (10) fetched from the database) for ID = 1
execute query; a database trigger had fired and UPDATED ID 1 value from 10 to 13 (i.e. 10 + 3)

Obviously, it *works*.

Attachment: form_trigger.png
(Size: 8.63KB, Downloaded 1132 times)

Report message to a moderator

Re: Receiving Form [message #552296 is a reply to message #552216]

Tue, 24 April 2012 23:50

furqan
Messages: 115
Registered: January 2012
Location: Hyderabad

Senior Member

thank a lot....!!! really thank you....
it got solved and works fine at the movement.
i really thank you all for your kind help and interest in my topic.again thanks a lot....

Report message to a moderator

Previous Topic:	restrict user to enter serial numbers of a product in 3 level block
Next Topic:	Trigger for SHIFT+F4

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 05 21:34:21 CDT 2024