Vendor payment history in multicompany

If you go from single company to multi-company, Vision doesn’t populate the “paid this year” and “paid last year” fields on the accounting tab for vendors.

If you look up in the help section, these fields are even editable and it’s very unclear how the figures get in there in the first place.

If you want to have those fields up to date, you can run this little handy script that I wrote every night in a scheduled workflow:

ALTER procedure fmv_pr_admin_update_vendor_paid_amounts
(@vendornumber varchar(12)=”)
as
BEGIN

/* 1/9/10 LS:
this procedure uses the vouchers table joined on the ledgerap table do determine checks paid on vouchers
and total amount paid per year for a vendor.
NOTE: the company(entity) that the check belongs to (paid from) is not determined by the ORG column in the ledgerap table
it is determined by the COMPANY field in the vouchers table
*/

/*
NOTE: to run for all vendors, leave the param blank (not null)
*/

update vea
set thisyear1099 = isnull(pmts.paidthisyear,0)
, lastyear1099 = isnull(pmts.paidlastyear,0)

–select vea.company, isnull(pmts.paidthisyear,0) as ‘paidthisyear’, isnull(pmts.paidlastyear,0) as ‘paidlastyear’

from veaccounting vea
left join
(
SELECT
vo.company
, LedgerAP.Vendor
, SUM(case when datepart(yyyy,ledgerap.transdate) = datepart(yyyy,getdate()) then AmountSourceCurrency else 0 end) AS ‘paidthisyear’
, SUM(case when datepart(yyyy,ledgerap.transdate) = datepart(yyyy,getdate())-1 then AmountSourceCurrency else 0 end) AS ‘paidlastyear’

  FROM LedgerAP
inner JOIN VO
ON LedgerAP.Voucher = VO.Voucher AND LedgerAP.Vendor = VO.Vendor

WHERE TransType=’PP’ AND SubType’X’
GROUP BY vo.company, ledgerAP.Vendor
) pmts

on vea.vendor = pmts.vendor
and vea.company = pmts.company

where vea.vendor like ‘%’ + @vendornumber + ‘%’

END

Comments or questions for Loren?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s