Using IM to Import AP Transactions (Advanced ODBC)

Today, in part two of our AP integrations through IM, I will show you how just using one feature of IM (Advanced ODBC), we can avoid a lot of manual work and make the process  a lot simpler and faster.

SQL Code Used in Header:
SELECT BatchID, VendorID, DocumentDate, Description, sum(distributionamount) as amount, left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8) as DocNumfrom AP2.csv

group by BatchID, VendorID, DocumentDate, Description,left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8)
order by left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8)

SQL Code Used in Detail:
select DocNum, Amount, ExpenseAccount,DistributionType, DistributionReference
from
(select left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8) as DocNum, distributionamount as amount, expenseaccount, '6' as distributiontype, distributionreference
from ap2.csv
union all
select left(expenseaccount,3)+'-'+BatchID+'-'+left(VendorID,8), -sum(distributionamount), left(expenseaccount, 3)+'-2000-00', '2', ''
from ap2.csv
group by left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8),left(expenseaccount, 3)+'-2000-00') GAPD
order by DocNum by left(ExpenseAccount, 3) +'-'+ BatchID+'-'+left(VendorID,8)