How are 1099s calculated in GP? I've done a recent upgrade from GP 9 to GP 2010 and it almost seems like the 1099s went from calculating the amounts by the DOC date, but now it's using the DATE1 column which is the apply date in the PM30300 table. Does anyone have a good understanding of this? What tables are in play here? Thanks in advance.
*This post is locked for comments
Glad you sort it out!!!!
I actually just entered some test data and traced it down and think I figured it out. I full joined this query with the actual PM00204 Table and my system (almost) balances with the exception of a few Box Numbers that are off. They may have been manually changed this year so far. But given that out of 12,000 Rows this returns from my system and only 30 are not a 1:1 match, I would say this is a pretty reasonable query to match up transaction data to the summary data.
SELECT
RTRIM(VENDORID) as [VENDORID]
,MONTH(CASE DOCTYPE WHEN 5 THEN DATE1 WHEN 6 THEN DOCDATE END) as [PERIODID]
,YEAR(CASE DOCTYPE WHEN 5 THEN DATE1 WHEN 6 THEN DOCDATE END) as [YEAR1]
,SUM(TEN99AMNT - Credit1099Amount) as [TEN99AMNT]
,DEFTEN99TYPE as TEN99TYPE
,DEFTEN99BOXNUMBER as TEN99BOXNUMBER
FROM PM30300 WITH(NOLOCK)
WHERE 1=1
AND YEAR(DATE1) = 2011
GROUP BY VENDORID,
MONTH(CASE DOCTYPE WHEN 5 THEN DATE1 WHEN 6 THEN DOCDATE END),
YEAR(CASE DOCTYPE WHEN 5 THEN DATE1 WHEN 6 THEN DOCDATE END),
DEFTEN99TYPE , DEFTEN99BOXNUMBER
ORDER BY 1,2,3
Ok then you can play with the following ones then you know what to do to remove the vendor filter:
**** All paid vouchers for each vendor record as per the specified Apply Date range
Select APTVCHNM, APTODCNM,
sum(TEN99AMNT) as TEN99Total,
sum(APFRMAPLYAMT) as AppliedTotal
from PM30300 where VENDORID = 'XXX'
and DATE1 between '2010-01-01' and '2010-12-31'
group by VENDORID, APTVCHNM, APTODCNM
**** All paid vouchers that have total applied amounts that are not equal to their total 1099 amounts for the specified Apply Date range.
Select (b.AppliedTotal - b.TEN99Total) as DIFF, * from
(select VENDORID, APTVCHNM, APTODCNM,
sum(APFRMAPLYAMT) as AppliedTotal,
sum(TEN99AMNT) as TEN99Total
from PM30300 where VENDORID = 'XXX'
and DATE1 between '2010-01-01' and '2010-12-31'
group by VENDORID, APTVCHNM, APTODCNM) b
where b.TEN99Total <> b.AppliedTotal
**** Total difference between applied amount and 1099 for each vendor.
Select sum(DIFF) as TotalDIFF from
(select (b.AppliedTotal - b.TEN99Total) as DIFF, * from
(select VENDORID, APTVCHNM, APTODCNM,
sum(APFRMAPLYAMT) as AppliedTotal,
sum(TEN99AMNT) as TEN99Total
from PM30300 where VENDORID = 'XXX'
and DATE1 between '2010-01-01' and '2010-12-31'
group by VENDORID, APTVCHNM, APTODCNM) b
where b.TEN99Total <> b.AppliedTotal) a
If the PM00204 table is damaged, the 1099 amounts may not reconcile with the actual transaction data
I'd like to open this one back up for discussion. I am actually working on a big project now with a large population of 1099 information for this year. We create invoices from another system (Peoplesoft) and basically integrate them into GP. We noticed that this whole year we have one of our expense types not being 1099 amounts when they should have been and I get to fix it. We are talking about 3,000 vendors, so there's no way we are going to update the 1099 amounts in the summary boxes. It's going to be a big SQL data fix. I'm going to go all the way back to the transaction level, update 1099 amounts on transactions, then I will be recalculating all 1099 amounts by box number, by period, by vendor for this current year.
I've really been trying to figure out GP's logic here and I can for the most part, but it seems like there is something missing. Francisco, I tried different variations of your script and it is not tying out with what GP is actually calculating. Here's an example. I have a 6,000 1099 invoice from 2009 and then a 6,000 1099 credit memo from 2010. They were finally applied to each other in 2011. That leaves me with a 2011 1099 Amount of 6,000 when in fact this was just an error (invoice/credit memo to offset each other). GP Shows nothing in the 1099 Box for this year, but my calculation (from your above script) shows that we should send out a 1099 for 6,000 this year for this person, when obviously that should not be the case.
I've tried querying everything from the PM30200 Date Invoice Paid off Field, what you have above, some logic from a 1099 Report that someone here wrote years ago, (which I found now is wrong). It just seems like there is far more logic involved than a straight select.
Because what if I have a 1000.00 invoice with 200.00 of that being 1099 amount. Then I relieve that invoice with 3 separate transactions over 3 periods. A 300.00 credit memo (where 250.00 of that is 1099 Amount), a $300 Credit memo where none of it is 1099 amount, and then a $400.00 Check for the remainder. All of these 3 are in different periods. In which period does that 200.00 show up as the 1099 amount for $200.00?
Just to add a little as to why the apply date is important. The system doesn't know whether the payment will be applied to a 1099 invoice when the payment is made. It isn't until the payment is applied that the system considers it a 1099 payment.
Yes here is the scrlipt to access the 1099 information
=====
select sum(TEN99AMNT) as T_99Sum, sum(APFRMAPLYAMT) as AppliedSum from PM30300
where VENDORID = 'XXX'
and DATE1 between '2010-01-01' and '2010-12-31'
group by VENDORID
=====
if you want all vendors just tweak it
=====
select VENDORID, APTVCHNM, APTODCNM,
sum(TEN99AMNT) as T_99Total,
sum(APFRMAPLYAMT) as AppliedTotal
from PM30300
where DATE1 between '2010-01-01' and '2010-12-31'
group by VENDORID, APTVCHNM, APTODCNM
Thanks Warren. That makes sense. I understand that the IRS does not recongize them until they are paid. So my next question is, is it the APPLY date that the system is looking at in order to calculate it?
1099s should show the amount PAID during the year, not the amount invoiced. So if a vendor invoice is dated 12/15/10, entered on 12/31/10, hits the GL with the expense in 12/10, but not paid until 1/15/11, then it should not be included on the 1099.
Been a while since looked at V9 in depth, but do know that GP2010 does the above.
Warren
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,321
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
