DMT Powershell - extract from SQL and update through DMT

Kinetic ERP
, ,
1

Does anyone have experience using the GitHub/DotNetIT scripts to extract data directly from SQL and then load through DMT?

We just started using these scripts with some BAQ’s to automate some simple tasks and they work great. But, we are wanting to do a more complex query that is easier to write in SQL. When we reached out to support to inquire about installing DMT on the SQL Server, we were told there shouldn’t be a problem. However, we got a strong caution from them given these scripts are 7 years old and of course they recommended we hire a consultant.

2

Leaning on DMT to automate business processes is probably as ancient of a practice as whatever scripts you’re looking at. I use DMT seldomly, mostly for mass update stuff (warehouse adds a bunch of new bins and I need to add those, set primary bins on parts, and mass transfer them). I use BPM’s or the API for day-to-day stuff.

What are you trying to do?

2 Likes
3

We have a certain inventory part (X) that gets loaded onto a job by engineering. On occasion, we will not have any quantity of this part on-hand, but we will have 3 individual parts (A, B, C) that can be used in place of part X. We want to locate part X by JobNum, AssemblySeq, & MtlSeq, and then update that Job and AssemblySeq to add parts A, B, and C where the MtlSeq is incremented, for each of the three parts, by 10 from the last MtlSeq that already exists under the Job-Asm in question.

Here’s an example: this part is on the Job-Asm with Mtl 130, required qty of 2, and we would like to add 2 each of the constituent parts. Since the highest MtlSeq for this Assembly is 320, the MtlSeq for the new parts will be 330, 340, and 350 respectively.

image

Also, what do you mean by the API? Epicor functions or something else?

4

Staring with 10.2ish, Epicor added REST endpoints you can call to get data out programmatically, or even post updates. Yes, functions are a part of that (and IMO best practice is that you should wrap all production external calls into a function) but they’re not exactly the same thing.

BPM (which can also use functions, but again, not necessarily) exist to automate business rules. BPM’s have been around longer than the API. I started with 9.0, and they were in the system already. A BPM would be able to automate your process here. Jobs are a little annoying since you have to unengineer and re-release them, but material tweaks shouldn’t be too bad, like adding/changing ops would (op changes require schedule changes).

However, I also wonder if you set up your BoM’s differently, that might also work. Is Mtl 130 a sub-assembly comprised of parts A, B & C?

5

Thank you for your response. Unfortunately, I don’t think setting up our BOM’s differently works in this scenario, we are a custom design shop and these are all purchased materials, not assemblies. Either you have part A or you need part B, C, D to quickly assemble part A. We are trying to keep inventory in check without having someone manually updating BOM’s.

6

I’ve not made the extract part work, but I’ve got a nifty Powershell/DMT windows scheduler task that uploads any XLSX file in a folder as a GL Journal transaction. I would hope that this template might help you modify those older scripts to work for the DMT/BAQ extract command, and I know there are lots of Powershell articles on running SQL scripts. I’m thinking you could run a stored procedure or script via the system agent to drop the files and use this script below to bring it in… good luck!

This is the guts of the Windows Task Scheduler task:

powershell.exe -NoProfile -NoLogo -NonInteractive -ExecutionPolicy Bypass -File "CeridianDayforceGLDMT_TEST.ps1"

CeridianDayforceGLDMT_TEST.ps1 is this:

#DMT Automation Example 1

$basePath = Get-Location
$logPath = “$basePath” + “\log”
$DataPath = “$basePath” + “\Data”
$logFile = “$logPath” + “\LoadLog_” + $(Get-Date -Uformat “%Y%m%d%H%M%S”) + “.txt”
$DMTPath = "C:\Epicor\ERP11\LocalClients\[Instance]\DMT.exe " #-DisableUpdateService "
$User = "manager"
$Pass = 'yourpassword'
$Source = "\\server.ashworth.com\EpicorData\CeridainDayforceGL"
$Processed = "\\server.ashworth.com\EpicorData\CeridainDayforceGL\Processed"
$dmttemplate = "`"GL Journal Combined`""
$EpicorConfig = “Ashworth”
 
Get-ChildItem -Path $Source *.xlsx |   #–Recurse
Foreach-Object { 
                    #Load Data
                    $FName = "`"" + $_.FullName + "`""
                    Start-Process -FilePath $DMTPath -ArgumentList "-NoUI -NoCompleteLog -NoErrorInput -DisableUpdateService -User $User -Pass $Pass -ConfigValue=$EpicorConfig -Add -Update -Import $dmttemplate -Source $FName  " -Wait 
                    Move-Item $_.FullName -Destination $Processed
                    #Write-Host $FName
                }


#This is for a separate job is to clean up afterwards that removes files Older than 90 days
#Get-ChildItem $Processed | Where-Object {$_.CreationTime -lt (Get-Date).AddDays(-90)} | ForEach-Object { Remove-Item $_.FullName –whatif }

Hope that helps

1 Like
7

If A can be produced from B, C & D, it’s an assembly regardless of whether or not you can also purchase A by itself. The system will actually handle that situation easily. If A is in stock, use A, if not, create job to make A with B,C & D as material. It’s cleaner because you’ve separated out the supply of A from the mfg of the parent part.

You can also set up alternate methods for parts, the default one using A, the other with the sub-materials. This is more manual and retains the issue where you muddle the mfg of A with the parent, but the resulting job is flat with 1 assembly.

You can still go with your original idea. You can write a BPM that checks on-hand qty at the time of job creation and monkeys with the job materials. That’s just working against the system rather than with it. DMT is the same thing, but even more fiddly.

1 Like