Email Alerts

[Nina Tulic]

Hi Team,

We have a client that wants an email alert sent out each time free stock is equal to or less than the minimum stock on hand level for a location.
Free stock for this client is calculated as follows:

SOH - Backorders + Outstanding Purchase Orders

Is this possible in Jiwa and if so how?

Thanks
Nina

[Mike.Sheen]

email alert sent out each time free stock is equal to or less than the minimum stock on hand level for a location.
Free stock for this client is calculated as follows:

SOH - Backorders + Outstanding Purchase Orders

Is this possible in Jiwa and if so how?

Thanks
Nina

Hi Nina,

It seems like you want to notify a purchasing officer when a stock item needs to be reordered, in a dynamic fashion.

You could tackle this by:

• Adding a SQL trigger to insert into a custom table the Part No. based on your rules. The trigger would not insert into the table if the item already has been added. The IN_SOH table and maybe IN_Orderlevels tables should have triggers on them
• Adding a scheduled plugin to check the table for entries, and send the email notification and then deleting the entries in the custom table

The above works well for situations where there are a large number of products and/or SOH transactions. You could also do away with the triggers and just run a query on a schedule to populate the custom table if the frequency of the schedule and the time taken for the query is not going to cause performance issues.

Or you could simply generate a report on a scheduled basis (once a day?) and email it to the desired recipient(s) if any products meet the criteria.

Or you could generate a Jiwa To-Do instead of an email. This may be better as the To-Do will be able to add a hyperlink to the message for the product(s) allowing the user to simply click on the link to drill to the product to get more detail.

Which is preferred? If you can tell us the number of items, number of warehouses and how often you want the email/notification sent then we can work out what's best - but consider the idea of a To-Do instead of an email also.

You'll need to also tell us how we determine the recipient(s) - I suggest a system setting specifying a user group and all members of that particular user group are emailed. That may mean only one staff member is a member of the recipient group, but if the purchasing officer retires, goes on leave or whatever, then just adding a user to the group will see them getting the notifications also or instead of (if you remove the retired staff from the group).

Also - do you want to send a notification for each individual item, or collate all items into one notification?

[Nina Tulic]

Hi Mike,

The clients preferred method of notification delivery is email, being one of the business owners he is usually not in front of his workstation but always carries his phone.

They have an inventory list of just under 1500 items across 2 main warehouses and would like an email sent out per item each time the rule applies, I have warned the owner that this could potentially result in numerous emails being sent out for a single item however he is happy with this. Having said that to give the function flexibility could the frequency and consolidation of all items into a single email be controlled through a system setting?

And lastly, your idea of determining the recipient(s) would work well as it does provide a good level of flexibility.

Thanks
Nina

[Mike.Sheen]

Hi Nina,

Attached is a plugin which should do what you want.

Once imported, you should extract from the documents tab of the plugin the two documents - One is a SQL Script which will need to be run against the database, and the order is a user group which should be imported and saved.

After importing the user group, you should add the desired staff members to the group. Only members of the "Reorder Notification" group will receive emails when items require reordering.

This plugin requires that the plugin scheduler service is running. I have configured the schedule to run every minute.

Plugin Reorder Notifications.xml

Sample plugin

(566.46 KiB) Downloaded 350 times

Mike