Home » Developer & Programmer » Forms » Trigger to Reduce Quantity (Oracle Forms 6i, Database 9i, Win XP)
icon4.gif  Trigger to Reduce Quantity [message #416257] Fri, 31 July 2009 06:48 Go to next message
Thunder2777
Messages: 2
Registered: July 2009
Location: Pakistan
Junior Member
Hi All

I have one form "Cash" with two tables Cash Master and Cash Detail (Master Detail relationship).

Other Table "Product" which serves like a Product Master (includes all product related info e.g. Product ID, Unit Price, Quantity, Lead Time, Re Order Level... ).

Can you tell me how to write a trigger which will Reduce the Inserted / Updated / Deleted Quantity form table "Product" After it is Successfully inserted or Updated or Deleted into "Cash Detail" Table.

How I know which products have been inserted or updated or Deleted in Cash Detail which will then be Inserted, Updated or deleted in Product?

Where should I place this code or trigger, I mean at Form, Block or Record level because Cash form is used to take cash orders and when I save one order (One Order and Many Items , One to Many relation), it clears all fields and increase the order no by one so that the next order details can be added
by using Clear Blocks and Adding Old Order ID with 1.

Any comments

Thanks in Advance.

Best Regards
Thunder2777

Report message to a moderator

Re: Trigger to Reduce Quantity [message #416457 is a reply to message #416257] Mon, 03 August 2009 00:08 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
I believe that you have described two problems - (1) keep an inventory count and (2) number the invoices ('without holes').

You have at least two choices in maintaining the total number of items for a product. My preference is NOT to have the field at all but to maintain the number by summing the receipts and subtracting the sales everytime it needs to be displayed. For this exercise you could maintain the count by subtrating the number on the "Cash Detail" line in a 'Pre-Insert' trigger. (You could verify that there are enough items to satisfy the order.) Then in a 'Pre-Update' trigger get the existing "Cash Detail" count and if different to the new count change the "Product" count. (Again you could verify that there are enough to satisfy the order.) In the 'Post-Delete' for "Cash Detail" you would add the 'not required' items back to the "Product" count.

To increment the Order Number use the "max+1" technique in a database trigger and display the value in a nondatabase item which you would populate in a 'Post-Insert' trigger on "Cash Master".

David

Report message to a moderator

Re: Trigger to Reduce Quantity [message #416732 is a reply to message #416457] Tue, 04 August 2009 02:18 Go to previous messageGo to next message
Thunder2777
Messages: 2
Registered: July 2009
Location: Pakistan
Junior Member
Thanks Djmartin

But my question was how to know that the end user has selected which products or items e.g. User selects item 01, item 05 and item 8 and specify their quantity e.g. 5, 7, 10 respectively.

Then the user saves the order and starts the next order.

Between First and second order, I want to reduce the quantity of Products / Items so that end user can have the updated stock or Product listing in LOV.

What would be the code for that trigger?

Order no is incremented with the same approach max+1. Thanks for the guidance.

Also their is quantity check trigger which allows the user to enter the quantity within the limit or quantity available. If user tries to enter higher quantity then a warning message appears to inform user to reduce the quantity.Thanks for the guidance.

Best Regards
Thunder2777

Report message to a moderator

Re: Trigger to Reduce Quantity [message #416884 is a reply to message #416732] Wed, 05 August 2009 00:20 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
As I said "For this exercise you could maintain the count by subtra[c]ting the number on the "Cash Detail" line in a 'Pre-Insert' trigger. (You could verify that there are enough items to satisfy the order.) Then in a 'Pre-Update' trigger get the existing "Cash Detail" count and if different to the new count change the "Product" count. (Again you could verify that there are enough to satisfy the order.) In the 'Post-Delete' for "Cash Detail" you would add the 'not required' items back to the "Product" count."

David

Report message to a moderator

Previous Topic: Cursor in program unit
Next Topic: forms to excel
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Sep 20 09:26:10 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.