Correction on last entry…

In my last entry I gave you a stored procedure to calculate paid amounts for this year and last year for vendors.

There was an error in the select statement where it used the “paymentdate” field in the voucher table instead of the “paidperiod”

The paidperiod field is the correct one to use… here is the new procedure:

ALTER procedure [dbo].[fmv_pr_admin_update_vendor_paid_amounts]
(@vendornumber varchar(12)=”)

/* 1/9/10 LS:
this procedure uses the vouchers table joined on the ledgerap table to 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, isnull(pmts.paidthisyear,0) as ‘paidthisyear’, isnull(pmts.paidlastyear,0) as ‘paidlastyear’

from veaccounting vea
left join
, LedgerAP.Vendor
, SUM(case when cast(left(vo.paidperiod,4) as int) = datepart(yyyy,getdate()) then AmountSourceCurrency else 0 end) AS ‘paidthisyear’
, SUM(case when cast(left(paidperiod,4) as int)  = 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, ledgerAP.Vendor
) pmts

on vea.vendor = pmts.vendor
and =

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


Comments or questions for Loren?

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

You are commenting using your 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