Importing AR Data from CMC to ERP

Owned by Charles Torres
Last updated: Jul 25, 2024 by Himanshu Shah
3 min read

inPrerequisite: Populate DivisionConversion Table for the ERP-[CompanyName]-Staging database.

1.) Create Miscellaneous Charge codes MISCLN and BEGBAL.

     This can be found in the MTSMiscCharge table. This needs to be done from ERP.

  1. Create customer called “MISCLN” in ERP with default values. This is used in AR beginning balance procedures.

Set IsUseCustomerConversionDivision in ClientSetting table to '1' if CustomerConversion table is being used..

(ie: UPDATE ClientSetting SET IsUseCustomerConversionDivision = 1)

Bank Code in Stored Proc. spEtlCmcCashReceiptSourceToARStaging must be changed to company Bank Code. This procedure sometimes has some hard coded values for start date or division code, make sure you change it per customer migration requirements.

(ie: UPDATE CashReceiptRaw SET BankCode = '3' where CompanyCode = '01')

Set Return Charge Code in Division Master under the Logistics and Invoicing tab to a Misc. Charge code.

Modify ArImportLocation & ArArchiveLocation columns in  ClientSetting Table in Customers SQL ERP DB to folder on SQL Server Drive.

ex: ArImportLocation=C:\ArImportFiles  and ArArchiveLocation=C:\ArImportFiles\Archive

Get Customer Master Template.xlsx from  Import Templates. Populate customer template for Customer and Location tabs, Use SQL Statement to add Customers then Locations.

Beginning Balances:

Run ARV35095ERP aging to get open balances. Creates file OPENINVS.csv. Get AR Open Invoices Template.xlsx template from Import Templates.

Take data from OPENINVS.csv and insert into the appropriate columns of the AR Open Invoices Template.xlsx and use the SQL Statement in the template to add beginning balances invoices to ERP.

To ERP Logistics > Invoice then Update and Register invoices one accounting period at a time. Once all invoices have been registered, run AR Aging and balance to the CMC Aging report. 

 

Get Items Template.xlsx  from  Import Templates. Populate items template for UOM and Item Master. Use SQL Statements to add UOM and Item Master codes to ERP.

Run ARV39271ERP in CMC against customer DB. This Creates file to copy to ERP SQL Server folder (ex: C:\ArImportFiles).

Files in /dbc/txt/unpr directory:  tot_CCYYMMDDHHMMSS_CO.txt, dtl_CCYYMMDDHHMMSS_CO.txt, rem_CCYYMMDDHHMMSS_CO.txt, rcp_CCYYMMDDHHMMSS_CO.txt

 

Aeros Import Invoices: Receivables > Utilities > Import Receivables

Select Import the invoice data records click Next Button

Select Company/Division from drop down.

Select Process all files and display data for import click Next Button

 

Grid will populate data from CMC extract files. Invoice Header, Invoice Detail and Invoice Detail Misc. Charge TAB will contain invoice data.

Errors will display in Records With Errors (Will not Import) TAB.

Example of error messages

Click Import All Valid Records Button.

 

Aeros Import Payments: Receivables > Utilities > Import Receivables

Select Import the payments data records click Next Button

Select Company/Division from drop down.

Select Process all files and display data for import click Next Button

Grid will populate data from CMC extract files. Payments to Import TAB will contain payment data.

Errors will display in Records With Errors (Will not Import) TAB.

Click Import All Valid Records Button.