Here’s the powershell script.
<#
.SYNOPSIS
Add comments to unapproved and approved Purchase Order Details csv file
.DESCRIPTION
The script loads the csv file using DMT.
Any errors are put into the error file.
The error file is used to generate a csv file with the approve flag set to false
The file is then loaded.
Finally, the error file is used to generate a csv file with the approve flag set to true, to return the records to there original approval status
.INPUTS
None
.OUTPUTS
None
.NOTES
Version: 1.0
Author: Andrew Clements - Cobra Consulting Limited
Creation Date: 30/10/2019
Purpose/Change: Initial script development
.DISCLAIMER
THIS SAMPLE CODE ON “AS IS” AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
IN NO EVENT SHALL COBRA CONSULTING LIMITED BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) SUSTAINED BY YOU OR A THIRD PARTY,
HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT ARISING IN ANY WAY OUT OF THE USE OF THIS SAMPLE CODE,
EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
.
#>
#----------------------------------------------------------[Declarations]----------------------------------------------------------
$DMTPath = “C:\Epicor\ERP10.1Client\Client\DMT.exe”
$User = ???
$Pass = ???
$Env = "net.tcp://???”
$DMTLoad = “PO Combined”
$Config = “EpicorERPTest”
$Source = “C:\temp\PODetailNotes.csv”
$Log = “C:\temp\PODetailNotes.log”
$completeLog = “C:\temp\PODetailNotes_CompleteLog.txt”
$errorLog = “C:\temp\PODetailNotes.csv.Errors.Reprocess.csv”
$unapprove = “C:\temp\unApprove.csv”
$approve = “C:\temp\Approve.csv”
$approveTmp = “C:\temp\ApproveTmp.csv”
#-----------------------------------------------------------[Functions]------------------------------------------------------------
#Load Data $
Start-Process -Wait -FilePath $DMTPath -ArgumentList “-ConnectionUrl $Env -User $User -Pass $Pass -Add=false -Update=true -Import ““PO Combined”” -Source $Source -NoUI -ConfigValue $Config -logfile $Log”
create the unapprove file based on the records in the errorfile generated by DMT, adding the appove column set to false
Import-Csv $errorlog |
Select-Object *,@{Name=‘Approve’;Expression={‘false’}} |
Export-Csv $unApprove -NoTypeInformation
#load the unapprove records
Start-Process -Wait -FilePath $DMTPath -ArgumentList “-ConnectionUrl $Env -User $User -Pass $Pass -Add=false -Update=true -Import ““PO Combined”” -Source $unApprove -NoUI -ConfigValue $Config -logfile $Log”
create a temporary unapprove file based on the records in the errorfile generated by DMT, removing all but the first two columns
(Import-CSV $errorlog -Header 1,2,3,4,5,6,7,8 |
Select “1”,“2” |
ConvertTo-Csv -NoTypeInformation |
Select-Object -Skip 1) -replace ‘"’ | Set-Content $approveTmp
#create the file to reapprove the records that were unapproved, adding the approve column set to true
Import-Csv $approveTmp |
Select-Object *,@{Name=‘Approve’;Expression={‘true’}} |
Export-Csv $approve -NoTypeInformation
#load the reapprove column
Start-Process -Wait -FilePath $DMTPath -ArgumentList “-ConnectionUrl $Env -User $User -Pass $Pass -Add=false -Update=true -Import ““PO Combined”” -Source $approve -NoUI -ConfigValue $Config -logfile $Log”
