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
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
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
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
Email list to amy
Run Import Vouchers program under ERP Payables > Utilities.
Scripts to be run against the CMC_Migrate DB created from Convert CMC AP Data to ERP AP Step.
Run SQL Script '1 - CMC Import - Create Scrub Tables,sql'
Run SQL Script '2 - CMC Import - Voucher Scrub.sql'
Run Pre Requisite Company / Division Conversion Section at top of Script 2. No SQL data returned means scrubbing process completed successfully.
If SQL data is returned possible issues could be:
Missing Vendor(s), GL Account(s), Accounting Period(s), Bank Code(s).
Run SQL Script '3 - CMC Import - Vouchers Insert.sql'
Set proper start/end year (@RunBegYear/@EndRunYear) for data to be migrated. Then set proper Month (@RunMonth) when on current year. Search for these variables in scripts.
Comment and Uncomment code based on migration year and/or month.
Run SQL Script '4 - CMC Import - Cancelled Vouchers Insert.sql'
Set proper start/end (@RunBegDate,@RunEndDate) dates at top of script.
Run SQL Script '5 - CMC Import - Cancelled Vouchers KeyOffs.sql'
Set proper start/end (@RunBegDate,@RunEndDate) dates at top of script.
Run SQL Script '6 - CMC Import - Payments Scrub.sql'
No SQL data returned means scrubbing process completed successfully.
If SQL data is returned possible issues could be:
Missing Vendor(s), Voucher(s), GL Account(s), Accounting Period(s), Bank Code(s).
Run SQL Script '7 - CMC Import - Payments Insert.sql'
Set proper period (@RunYear, @RunMonth) to process payments.