Joana
asked on
DEVELOPER 2000 LOV/Cursor_Trigger
I have a customer order line which has
Line No
Style No
Colour
Size
Qty
If the clerk - when entering in the ordered item details - enters in a qty for the item that is higher than the qty
that exsists in the oracle stock_item table (called qty_on_hand) then a message appears saying 'There is not enough of this item in chosen colour. Please chose another colour or lower qty'. It then pops up with and LOV that has the avaiable colours for that styleno and size.
E.G
Line Style Colour Size Qty
No No
1 DS204 Olive 12 8
But there is only 5 of this colour and size for that style so the message adn lov should be activated and stop the user being able to tab till it is correct:
My current post_change trigger code is:
DECLARE
CURSOR CUR_Qty_OH IS
SELECT Qty_On_Hand
FROM Stock_Item
WHERE styleno = :customer_order_line.style no
AND colour = :customer_order_line.colou r
AND sizes = :customer_order_line.sizes ;
dummy_Qty_OH NUMBER;
LOVColour_dummy BOOLEAN;
BEGIN
IF NOT CUR_Qty_OH%ISOPEN THEN
OPEN CUR_Qty_OH;
END IF;
FETCH CUR_Qty_OH INTO dummy_Qty_OH;
IF :customer_order_line.quant ity > dummy_Qty_OH THEN
message ('There is not enough of this item in stock. Please lower the quantity or pick another colour from the list');
LOVColour_dummy := Show_LOV('LOVCOLOUR',15,10 );
END IF;
CLOSE CUR_Qty_OH;
END;
And my LOV Code for the colour is
select colour from stock_item where :customer_order_line.style no = stock_item.styleno and :customer_order_line.sizes = stock_item.sizes and qty_on_hand >= :quantity
Line No
Style No
Colour
Size
Qty
If the clerk - when entering in the ordered item details - enters in a qty for the item that is higher than the qty
that exsists in the oracle stock_item table (called qty_on_hand) then a message appears saying 'There is not enough of this item in chosen colour. Please chose another colour or lower qty'. It then pops up with and LOV that has the avaiable colours for that styleno and size.
E.G
Line Style Colour Size Qty
No No
1 DS204 Olive 12 8
But there is only 5 of this colour and size for that style so the message adn lov should be activated and stop the user being able to tab till it is correct:
My current post_change trigger code is:
DECLARE
CURSOR CUR_Qty_OH IS
SELECT Qty_On_Hand
FROM Stock_Item
WHERE styleno = :customer_order_line.style
AND colour = :customer_order_line.colou
AND sizes = :customer_order_line.sizes
dummy_Qty_OH NUMBER;
LOVColour_dummy BOOLEAN;
BEGIN
IF NOT CUR_Qty_OH%ISOPEN THEN
OPEN CUR_Qty_OH;
END IF;
FETCH CUR_Qty_OH INTO dummy_Qty_OH;
IF :customer_order_line.quant
message ('There is not enough of this item in stock. Please lower the quantity or pick another colour from the list');
LOVColour_dummy := Show_LOV('LOVCOLOUR',15,10
END IF;
CLOSE CUR_Qty_OH;
END;
And my LOV Code for the colour is
select colour from stock_item where :customer_order_line.style
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would question the suggestion of key next item if you are able to "tab-out" of the field using a method other than "tab" (e.g. using a mouse) though it does answer one aspect of your question if you are to be taken literally.
I would prefer to put that in a WVI trigger as well since the validation will be run whatever method is used to leave the field.
With respect to the NULL values, that is why I recommended you clear the record and sent the focus back to that item...as it will fail if they try to exit, witht he appropriate message.
Regards,
JT
I would prefer to put that in a WVI trigger as well since the validation will be run whatever method is used to leave the field.
With respect to the NULL values, that is why I recommended you clear the record and sent the focus back to that item...as it will fail if they try to exit, witht he appropriate message.
Regards,
JT
A post-change trigger is not the best way to do this (unless you are still working on a Forms2.3 or 3.0 system). Use a when-validate-item trigger instead (Forms4.0 through Forms6.0) to check the quantity. This will fire only and exactly at the time you need it to. A post-change trigger will also fire at query time, and that is wasted processing in this case. Using a key-next-item trigger is dangerous because there are too many other ways the user can move the cursor out of the item and avoid that trigger.
You would be better off (from a performance and reusability perspective) to put the query that checks the available quantity on hand in a database function (or packaged function), then call that function in your when-validate-item trigger.
To be completely safe, you need to repeat this check of available quantity in the pre-insert trigger (and pre-update trigger if quantity, color or size can be adjusted). Otherwise what happens if there are three available, and the clerk enters two different lines for two each? At when-validate-item time, it appears that there is sufficient quantity for each. You also need the commit-time (pre-insert and/or pre-update) triggers to cover you if two different clerks could possibly enter a line for the same item at almost the same time.
You would be better off (from a performance and reusability perspective) to put the query that checks the available quantity on hand in a database function (or packaged function), then call that function in your when-validate-item trigger.
To be completely safe, you need to repeat this check of available quantity in the pre-insert trigger (and pre-update trigger if quantity, color or size can be adjusted). Otherwise what happens if there are three available, and the clerk enters two different lines for two each? At when-validate-item time, it appears that there is sufficient quantity for each. You also need the commit-time (pre-insert and/or pre-update) triggers to cover you if two different clerks could possibly enter a line for the same item at almost the same time.
ASKER
Sorry i didn't expalin myself very well but i didn't know how to put what i actually wanted - turns out i had the code in the wrong kind Trigger - i had it in post change instead of in when validate - when i changed it it worked fine :-)
If you are succesfully bringing up the lov, you could simply clear the field that was previously entered (qty) and do a go_item back to that field.
You can attach this to the buttons of the LOV or put it in a when-validate-item trigger.
I assume that you are already using a WVI trigger here that is running the above code. If you clear the field and go_item back to it and then run your other code, this should ensure that a field must be entered.
Or am I missing something here?
Regards,
JT