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)=”)
as
BEGIN


/* 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 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 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 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