Triggers
-
Right then, im having trouble setting up a procedure, We have an MRP system which prints out daily recommendations each morning. I want this information to be mailed to the relevant user. The basics of what would happen is the Quantity of our stock would be measured against our Minimum Re-order level, if our quantity falls below this level it will then mail you to alert you that this item need's to be looked at. I would want this email to be seen each morning so it will gather all the items which have gone under the minimum re-order quantity and then mail it to the user. One problem is though i wouldnt want the user to get inundated with emails about the same items, can i put a stop on items which have been mailed already and put a staggering system on there so that it will remind them once about the item and then after five days...I don't even know if this can be done, its sounds good in theory though and i know it would help our buying department......
Thanks
Jonah
-
>>I don't even know if this can be done
Everything can be done. It is just matter of time and money. - this is what my ex-boss used to say
You can schedule a daily job to do the mailing. The job would have to maintain a record of what items the notifications were sent and when so you can go back and check against your 5 days limit.
PS. and as far as I can tell this has nothing to do with triggers...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
I thought Trigger was wrong terminology!! Ah well
I currently have my DTS package in place to extract the data from the AS400. I have my fields as well.
Planner Code:::Item No:::Quantity:::Minimum Quantity::: Date
LT TB123 -148 300 20060116
TT TB456 6450 2000 20060116
So this is basically pulling all the items out of our warehouse and you can see what the current quantity is and also our Minimum Quantity level. So because Item TB123 has fallen below the minimum quantity, it will mail this part along with the other parts which have fallen under this minimum quantity figure. Would anyone know the SQL to calculate the items which need to be mailed? My current Query looks like this:
SELECT AIMPDTA.TBPLAN.TBPLNR, AIMPDTA.TBPLAN.TBITEM,
AIMPDTA.TBPLAN.TBQTY, AIMPDTA.TBPLAN.TBOPDT,
AIMPDTA.GDWITM.GDMIN
FROM AIMPDTA.GDWITM, AIMPDTA.TBPLAN
WHERE AIMPDTA.GDWITM.GDWHSE = AIMPDTA.TBPLAN.TBWHSE AND
AIMPDTA.GDWITM.GDITEM = AIMPDTA.TBPLAN.TBITEM
GROUP BY AIMPDTA.TBPLAN.TBPLNR, AIMPDTA.TBPLAN.TBITEM,
AIMPDTA.TBPLAN.TBQTY, AIMPDTA.TBPLAN.TBOPDT,
AIMPDTA.GDWITM.GDMIN
TBPLNR=Planner Code, TBITEM=Item Number, TBQTY=Quantity, GDMIN=Minimum Quantity and TBOPDT=Date
Thanks, your help would be much appreciated!
-
I dont fully understand your table structure but I'm guessing you need somethiong like
WHERE ......
AND TBQTY <= GDMIN
Is TBQTY an absolute value for the Item No or is it a 'usage' type value where you may see more than 1 row for each Item No. If the latter then you would need to sum up all the usage of each item so that you only have one e-mail per item.
-
As an aside, I would write the query to use table aliases, like this:
SELECT T.TBPLNR, T.TBITEM,
T.TBQTY, T.TBOPDT,
G.GDMIN
FROM AIMPDTA.GDWITM G, AIMPDTA.TBPLAN T
WHERE G.GDWHSE = T.TBWHSE AND
G.GDITEM = T.TBITEM
GROUP BY T.TBPLNR, T.TBITEM,
T.TBQTY, T.TBOPDT,
G.GDMIN
It's more readable.
Furthermore, what you're doing is clearer if you use a JOIN rather than a WHERE clause in this particular case:
SELECT T.TBPLNR, T.TBITEM,
T.TBQTY, T.TBOPDT,
G.GDMIN
FROM AIMPDTA.GDWITM G
INNER JOIN AIMPDTA.TBPLAN T
ON G.G.GDWHSE = T.TBWHSE
AND G.GDITEM = T.TBITEM
GROUP BY T.TBPLNR, T.TBITEM,
T.TBQTY, T.TBOPDT,
G.GDMIN
-
Thanks for your help guys...Much appreciated
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply