epicor 9.05.701
Task: i created 2 custom fields in miscellaneous shipment entry.
Problem: The email fires but the TODAY date doesn’t automatically update at midnight. The TODAY date only updates when the record is added/updated etc.
Does anyone know how to fix this?
Thank you in advance
Has anyone come across a similar scenario?
Once the record is saved to the database, it isn’t going to automatically change unless a user or process updates it. Using TODAY() as the method of populating the field is fine, but in reality to the DB it isn’t any different from a user having input a date.
I have things that I want to check daily, and then trigger alerts. I do all of mine using SSIS (part of SQL Server) - that way I can have a SQL statement check for records matching a criteria, and then fire off an email to alert somebody. I do this for “Short Life Stock”, where the Expiry Date of a Lot is less than or equal to 10 days from today.
Thanks Mark, i understand.
Is there some SQL syntax that could be ran to kind of “refresh” the table?
OR
maybe i can add a further customisation of a tick box to each record and simply turn the tick on/off via sql each time?
Yep all possible. If you’re doing any kind of update, then you want to use REST services and not SQL updates though. I’m happy using SQL, because I fully understand that I’m only ever doing it read only, and also caching in the appservers isn’t any issue.
I wouldn’t be looking to update anything using a process in your scenario, just flag to a user and let them do something about it. Make the SSIS package run daily at a set time, and then email results. Your current DD BPM relies on something triggering an update to the MscShpHd table, where the SSIS package is purely time schedule.
thank you for this.
so, if i understand correctly, its possible in SSIS to say if the loan expiry date = today - then fire an email?
can you give me some more details about how the SSIS works? how to set up an auto-email based on a table/field criteria being met?
Just reviewing our server…we have microsoft SQL Server Management Studio
The simplest (and maybe hackiest) way to get your DD to fire wold be to schedule a Windows task, to run DMT via a powershell script, to “touch” the MscShpHd table at 12:01 each day.
Here’s a PS script I use to update a UD table, which has a DD tied to the field.
# This Script runs the Epicor DMT utility to update table UD05
# The change of UD05.Checkbox20 from 0 -> 1 initiates a Data Directive
# that generates the code to auto email shipping notices.
# This should be run at 4:00 PM EST every day
$DMTPath = "C:\Epicor\ERP10\LocalClients\PRD_102300\DMT.exe"
$User = "XXXXX"
$Pass = "XXXXX"
$Source = "C:\Temp\trigger.csv"
echo "Company,Key1,Key2,Key3,Key4,Key5,CheckBox20" > $Source
echo "MC,CKRU,,,,,1" >> $Source
echo "MC,CKRU,,,,,0" >> $Source
#Load Data
Start-Process -Wait -FilePath $DMTPath -ArgumentList "-NoUI -ConfigValue MATCOR -User $User -Pass $Pass -Update -Import UD05 -Source $Source "
It creates the CSV file (“trigger.csv”), then runs DMT, which basically toggles UD05.Checkbox20. A DD is set for when UD05.Checkbox20 goes from false to true, and builds the email to send.
hi - thanks for this…small problem is there is no DMT facility for misc shipment entry
