Confirm 1099 Amounts in Dynamics SL

As we near the due date for 1099 information to be submitted to the IRS, I thought I would share the SQL code I use to confirm that 1099 amounts match up with the payments that were made to each vendor. Sometimes AP enters vouchers before confirming the correct 1099 status of a vendor and the box is not selected when the Voucher is entered.

SL keeps 2 years of 1099 information in a table called ‘AP_Balances’. The amounts years are represented by CY (current year) and NY (next year). In the script, I am comparing the current year amounts in the AP_Balances table to the payment amounts in the APAdjust table based on the @begdate and @endDate defined at the top of the query.

If you have discrepancies, you will need to determine if they actually need to be changed (sometimes there is a reason they are off and it’s correct.) To make the change, you can go into ‘Initialize Mode’ then pull up the Vendor record in the Vendor Maintenance screen. On the 1099 tab, you will be able to edit the amounts in the appropriate boxes.

--make sure that system has been moved forward so reporting year is the 'current year'  --- if not switch APB.CY to APB.NY

declare @begdate datetime = '20190101'
declare @enddate datetime = '20191231'
declare @vendid varchar(10) = '%'
declare @only1099vendors varchar(1) = '1' --the checkbox in vendor maintenance : 1 yes, 0 no, '%' all
declare @onlyshowdiff varchar(1) = '1' --only show where calc'd AP <> total_1099:  1 yes, 0 no
declare @min1099amt float = '0'  --either 0 or number > 0 


if OBJECT_ID('tempdb..#temp') is not null drop table #temp;

SELECT
	v.vendid, v.Name, v.Addr1, v.Addr2, v.City, v.State, v.Zip, case v.vend1099 when 1 then 'Y' else 'N' end as vend1099, v.DfltBox, v.TIN, v.TINNAME, 
	APB.CYBox00 as BOX1, 
	APB.CYBox01 as BOX2, 
	APB.CYBox02 as BOX3, 
	APB.CYBox03 as BOX4, 
	APB.CYBox04 as BOX5, 
	APB.CYBox05 as BOX6, 
	APB.CYBox06 as BOX7, 
	APB.CYBox07 as BOX8, 
	APB.CYBox08 as BOX9, 
	APB.CYBox09 as BOX10, 
	APB.CYBox10 as BOX11, 
	APB.CYBox11 as BOX12, 
	APB.CYBox12 as BOX13, 
	APB.CYBox13 as BOX14, 
	APB.CYBox14 as BOX15,
	SUM(	APB.CYBox00 + 
			APB.CYBox01  +
			APB.CYBox02  +
			APB.CYBox03  +
			APB.CYBox04  + 
			APB.CYBox05  + 
			APB.CYBox06  + 
			APB.CYBox07  + 
			APB.CYBox08  + 
			APB.CYBox09  + 
			APB.CYBox10  + 
			APB.CYBox11  + 
			APB.CYBox12  + 
			APB.CYBox13  + 
			APB.CYBox14  ) as Total_1099,
	ISNULL(ap.APamt,0) as Calculated_AP_Amt
into #temp
from vendor v
	join AP_balances APB 
		on APB.vendid = v.vendid
full outer join (select vendid, SUM(case adjddoctype when 'ad' then -adjamt else adjamt end) as apamt 
					from apadjust
					where adjgdocdate >= @begdate and adjgdocdate < @enddate + 1
					group by vendid
					) ap on ap.vendid = v.vendid

WHERE  v.VendId like @vendid and v.vend1099 like @only1099vendors 

GROUP BY 
	v.VendId, v.Name, v.Addr1, v.Addr2, v.City, v.State, v.Zip, v.vend1099, v.DfltBox, v.TIN, v.TINNAME,
	APB.CYBOX00,
	APB.CYBox01,
	APB.CYBox02,
	APB.CYBox03,
	APB.CYBox04, 
	APB.CYBox05, 
	APB.CYBox06, 
	APB.CYBox07, 
	APB.CYBox08, 
	APB.CYBox09, 
	APB.CYBox10, 
	APB.CYBox11, 
	APB.CYBox12, 
	APB.CYBox13,
	APB.CYBOX14,
	AP.APAmt
ORDER BY vendid


if @min1099amt > 0 
delete from #temp 
where (Box1 < @min1099amt and Box2 < @min1099amt and Box3 < @min1099amt and box4 < @min1099amt and box5 < @min1099amt and  box6 < @min1099amt and box7 < @min1099amt and box8 < @min1099amt and box9 < @min1099amt and box10 < @min1099amt and box11 < @min1099amt and box12 < @min1099amt and box13 < @min1099amt and box14 < @min1099amt and box15 < @min1099amt and Calculated_AP_Amt < @min1099amt)


if @onlyshowdiff = '1'
delete from #temp where ABS(Total_1099 - Calculated_AP_Amt) < .001 

select * from #temp 







Feel free to share...

Leave a Reply

Close Menu