Importing CMC AP Data to ERP AP

Make sure all AP data is purged before running these scripts. Bring in Open AP Vouchers (APV33315ERP) prior to running scripts.

Type in APV33315ERP at main menu. These steps are for beginning balance.

Say 'y' to creating csv file

Exit CMC software to release hold on csv file

Go to SQL box

Right-click on CMC-Iowa, tasks, import data. The csv file name is apv33315e01.csv.

Open

Open

open a query

  UPDATE importdata
  SET Invoice = REPLACE(Invoice, '"', ''), 
  [GL Acct] = REPLACE([GL Acct], '"', ''),
  Division = REPLACE(Division, '"', '')

  TRUNCATE TABLE dbo.APBegBalance

  INSERT dbo.APBegBalance
  SELECT
  Invoice, Amount, Units, InvDte, RcvDte, [GL Acct], Division, Reference, Vendor, [Inv Comment], DueDte, [Exp Comment], [Disc Amount], Status, [Create Dte], CMCVhcr, Type  
  FROM dbo.importdata

transfer data to ERP-Iowa-Staging table. If you are not using conversion tables then use original tables; for instance use Glacct instead of GlAccountConversion, Vendors instead of VendorConversion in scripts below and adjust to appropriate columns in joins.

SELECT DISTINCT Vendor FROM [CMC-IOWA].dbo.APBegBalance a
LEFT JOIN ERP_Iowa_Staging.dbo.VendorConversion v
ON v.OldVendorCode = a.Vendor
WHERE v.VendorConversionID IS NULL

SELECT DISTINCT [GL Acct] FROM [CMC-IOWA].dbo.APBegBalance a
LEFT JOIN ERP_Iowa_Staging.dbo.GLAccountConversion g
ON g.OldGLAccount = a.[GL Acct]
WHERE g.GLAccountConversionID IS NULL

INSERT INTO APVoucherImportDetail 
(InvoiceNumber,
 Amount,
 Units,
 InvoiceDate,
 ReceivedDate,
 GLAccount,
 DivisionCode,
 VendorCode,
 InvoiceComment,
 DueDate,
 ExpenseComment,
 DiscountAmount,
 APVoucherImportStatusID,
 CreateDate) 
 SELECT a.Invoice,
    CAST (LTRIM(RTRIM(REPLACE(a.Amount,',',''))) AS DECIMAL(16,5)),
    CAST (LTRIM(RTRIM(REPLACE(a.Units,',',''))) AS DECIMAL(16,5)),
    CAST (a.InvDte AS DATETIME),
    CAST (a.RcvDte AS DATETIME),
    g.NewGLAccount,
    a.Division,
    v.NewVendorCode,
    a.[Inv Comment],
    CAST (a.DueDte AS DATETIME),
    a.[Exp Comment],
    CAST (LTRIM(RTRIM(REPLACE(a.[Disc Amount],',',''))) AS DECIMAL(16,5)),
    CAST (a.[Status] AS INT),
    CAST (a.[Create Dte] AS DATETIME)
FROM [CMC-Iowa].[dbo].[APBegBalance] a
INNER JOIN GLAccountConversion g 
ON g.OldGLAccount = a.[GL Acct]
INNER JOIN VendorConversion v
ON v.OldVendorCode = a.Vendor

Run Import Vouchers program under ERP Payables > Utilities. This may show error and one of the error is: could not map default bank. Make sure you have a default bank account set and associate all vendors to that default bank account seq no.

Scripts to be run against the CMC_Migrate DB created from Convert CMC AP Data to ERP AP Step.