DMT - AP Invoice Header Misc Charge

Epicor ERP 9
1

Hey folks,

I need some quick help with DMT. I have about 3500 variances that I need to correct with an AP Invoice Header Misc Charge. I have created the template in DMT and it works, but it only processes the 1st line on the template. Every line after the 1st fails with this error;

Company CPO InvoiceNum = BB5G22R MscNum = 0 Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Table: APIHAPInvMsc
Company=‘CPO’ VendorNum=‘778’ InvoiceNum=‘BB5G22R’ InvoiceLine=‘0’ MscNum=‘0’ RowIdent=’’: ForeignKeyConstraint APInvHedAPIHAPInvMsc requires the child key values (CPO, 778, BB5G22R) to exist in the parent table

I am assuming it can’t start a new Misc Charge and that is why it is erroring out. I have tried changing the MiscNum on every line starting with 0, but that isn’t working.

Can someone please help me figure out how to make this work, so I can upload more than 1 charge at a time?CPO-APHeaderCharge2.csv (176 Bytes)

2

It doesn’t solve your problem, but you could make a script or batch file to run DMT via command line, with one record per each time DMT is run.

Here’s a sample of a powershell script (you could easily do it as a BAT file too)

$DMTPath = "C:\Epicor\ERP10\LocalClients\E9_App_Env\DMT.exe"
$User = "_glbl_epicor"
$Pass = "password"

$Source = "C:\Temp\trigger.csv"

echo "Company,MscNum,InvoiceNum,VendorNumVendorID,InvoiceLine,MiscCode,Type,ScrMiscAmt,ScrDocMiscAmt" > $Source
echo "CPO,0,BB5FZWH,ESSE,1,PPV,A,-7.25,-7.25" >> $Source
Start-Process -Wait -FilePath $DMTPath -ArgumentList "-NoUI -ConfigValue E9_App_Env -User $User -Pass $Pass -Update -Import APInvMisc -Source $Source "

echo "Company,MscNum,InvoiceNum,VendorNumVendorID,InvoiceLine,MiscCode,Type,ScrMiscAmt,ScrDocMiscAmt" > $Source
echo "CPO,0,BB5G22R,ESSE,1,PPV,A,-6.69,-6.69" >> $Source
Start-Process -Wait -FilePath $DMTPath -ArgumentList "-NoUI -ConfigValue E9_App_Env -User $User -Pass $Pass -Update -Import APInvMisc -Source $Source "

  • Just repeat the last three lines for each record.

  • Only the 2nd of the 3 lines needs to be updated.

  • Change the $User and $Pass to match a valid user.

  • In the Start-Process line, replace E9_App-Env with the name of your environment (the one you select when DMT first launces), and APInvMisc with the table name (APInvMisc was just a guess).

If you already have an excel file with the 3500 records, use that to make the script lines. Then copy them to text file.

Edit: here’s your original file in Excel, with the formula to make those three lines for each record.
CPO-APHeaderCharge2.xlsx (10.1 KB)

3

Thanks Calvin. I will try that.

4

Please try it on a test company first. Or at least just make the script/BAT file only do 10 records on the first try, and then check the results.

5

Hey Calvin.

I worked with my boss and we were able to make a script that creates the commands for all 3500+ charges, but i can’t get it to work. It keeps telling me that it can’t connect to the AppServer. Do you have any time to look at it for me and see what i might be doing wrong? i don’t think i am far off.

6

Sure.

First a few things to look at…

  • I’m on E10, so the path to the DMT exe is probably different.
  • The command line options -(like NoUI -and ConfigValue) might be different for E9
  • The App Environment(‘E9_App_Env’’ in my sample), $User and $Pass variables should be what you’d enter when launch DMT in the GUI mode (#'s 1, 2, & 3 in image below).

image

Does DMT run from a command line when you manually enter it in a dos window?

If you upload a the script file I’ll take a look at it (only needs to have the first dozen lines)

Calvin