TAX Details for invoices PM

[jcw12000]

Hi

I am trying to join TAX information from TX3000 to Invoices. PM side. I see that DOCNUMBR in TX3000 sometimes have a VCH number and other RCT numbers. As I understand the RCT numbers are transactions that the source is a PO if I am correct.

Does someone have a script that I can use to link TAX information (As posted to the TAX GL) to the Invoices. Even a script that uses GL3000 would be great.

Thanks

 

[VictoriaYudin]

RCT numbers will show up in the TX30000 for payables invoices that were entered in the POP (Purchase Order Processing) module instead of the PM (Payables Management) module. These transactions will show up in the GL30000 and GL20000 with an ORDOCNUM matching the RCT number, the SERIES will be 4 and the ORTRXTYP will be either 2 (Invoice) or 3 (Shipment/Invoice).

Hope that helps. If you need more help, can you write back with a list of columns you are looking to show in your results?

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:

http://www.GPReportsViewer.com/gpreports.html

blog:

http://victoriayudin.com

 

[jcw12000]

I need to show all the paid invoices with their invoice date, document amount, document number, payment document number, payment voucher number, GL account number of the tax portion, tax amount.

 

[jcw12000]

Even if a Invoice has been entered in the POP I need the tax info of that transaction.

The TAX amount should be the amount that is in the TAX GL

 

[VictoriaYudin]

Typically the way to match the GL amount is to start with the GL. Otherwise you may be missing parts. For example, what if a General Ledger entry was entered against the tax account? You may not see it anywhere except the GL tables.

So I would start with the GL20000 and GL30000 tables, get all transactions for the tax account(s) you want and then link from there to the detail in PM and/or POP tables. Once you get the invoice numbers you can link to the payment information.

I do not have one script that will do everything you're looking for, but you should be able to piece it together from a number of different scripts I have on my blog here:

http://victoriayudin.com/gp-reports/payables-sql-views/.

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:

http://www.GPReportsViewer.com/gpreports.html

blog:

http://victoriayudin.com

 

[jcw12000]

Thats where I dont seem to find the link from the GL to get the invoice numbers

 

[VictoriaYudin]

You can determine where to link to from the GL based on the ORGNTSRC value. If it is PMTRX you can link to PM tables. If it is RECVG or POIVC, then it is POP - you can use the POP30300 table to find the invoice number based on the RCT number. You can link GL20000 to POP30300 on
POP10500.POPRCTNM = GL20000.ORDOCNUM
and POP10500.POPTYPE = GL20000.ORTRXTYP

The PM invoice number will be POP30300.VNDDOCNM


Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:

http://www.GPReportsViewer.com/gpreports.html

blog:

http://victoriayudin.com

 

[jcw12000]

What do you think about using the following:




----------------------------------------------------------
----------------------------------------------------------
----- Use for Payments and VAT ----------
----------------------------------------------------------
----------------------------------------------------------



SELECT P.[INVID],P.VENDORID Vendor_ID,P.DOCTYPE,
V.VENDNAME Vendor_Name,
V.VNDCHKNM Vendor_Check_Name,
V.[VNDCLSID] as VendorType,
CASE P.PYENTTYP
WHEN 0 THEN 'Check'
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Credit Card'
WHEN 3 THEN 'EFT'
ELSE 'Other'
END Payment_Type,
CASE
WHEN P.PYENTTYP in (0,1,3) THEN P.CHEKBKID
ELSE ''
END Checkbook_ID,
CASE P.PYENTTYP
WHEN 2 THEN P.CARDNAME
ELSE ''
END Credit_Card_ID,
P.DOCDATE Payment_Date,
P.PSTGDATE Payment_GL_Date,
P.VCHRNMBR Payment_Voucher_Number,
P.DOCNUMBR Payment_Document_Number,
P.DOCAMNT Payment_Functional_Amount,
coalesce(PA.APTVCHNM,'') Apply_To_Voucher_Number,
CASE PA.APTODCTY
WHEN 1 THEN 'Invoice'
WHEN 2 THEN 'Finance Charge'
WHEN 3 THEN 'Misc Charge'
ELSE ''
END Apply_To_Doc_Type,
coalesce(PA.APTODCNM,'') Apply_To_Doc_Number,
coalesce(PA.APTODCDT,'1/1/1900') Apply_To_Doc_Date,
coalesce(PA.ApplyToGLPostDate,'1/1/1900') Apply_To_GL_Date,
coalesce(PA.APPLDAMT,0) Applied_Amount,
coalesce(G.ACTNUMST,'') GL_Account_Number,
coalesce(G2.ACTDESCR,'') GL_Account_Name,
coalesce(G2.ACTINDX,'') GL_Account_INDX,
CASE D.DISTTYPE
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Payable'
WHEN 3 THEN 'Discount Available'
WHEN 4 THEN 'Discount Taken'
WHEN 5 THEN 'Finance Charge'
WHEN 6 THEN 'Purchase'
WHEN 7 THEN 'Trade Disc.'
WHEN 8 THEN 'Misc. Charge'
WHEN 9 THEN 'Freight'
WHEN 10 THEN 'Taxes'
WHEN 11 THEN 'Writeoffs'
WHEN 12 THEN 'Other'
WHEN 13 THEN 'GST Disc'
WHEN 14 THEN 'PPS Amount'
ELSE ''
END Distribution_Type,
coalesce(D.DEBITAMT,0) Debit,
coalesce(D.CRDTAMNT,0) Credit
into Invoice_Split
FROM
(SELECT [INVID],VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,
VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME
FROM PM30200
UNION
SELECT [INVID],VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR, DOCAMNT,
VOIDED, TRXSORCE, CHEKBKID, PSTGDATE, PYENTTYP, CARDNAME
FROM PM20000) P

INNER JOIN
PM00200 V
ON P.VENDORID = V.VENDORID

LEFT OUTER JOIN
(SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT
FROM PM10200
UNION
SELECT VENDORID, VCHRNMBR, DOCTYPE, APTVCHNM, APTODCTY,
APTODCNM, APTODCDT, ApplyToGLPostDate, APPLDAMT
FROM PM30300) PA
ON P.VCHRNMBR = PA.VCHRNMBR
AND P.VENDORID = PA.VENDORID
AND P.DOCTYPE = PA.DOCTYPE

LEFT OUTER JOIN
(SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT,
DSTINDX, DISTTYPE, DistRef, PSTGDATE
FROM PM10100
WHERE PSTGSTUS = 1 AND CNTRLTYP = 0
UNION
SELECT VENDORID, VCHRNMBR, CNTRLTYP, DEBITAMT, CRDTAMNT,
DSTINDX, DISTTYPE, DistRef, PSTGDATE
FROM PM30600 WHERE CNTRLTYP = 0) D
ON PA.VENDORID = D.VENDORID
AND PA.APTVCHNM = D.VCHRNMBR

LEFT OUTER JOIN
GL00105 G
ON D.DSTINDX = G.ACTINDX

LEFT OUTER JOIN
GL00100 G2
ON D.DSTINDX = G2.ACTINDX

WHERE P.DOCTYPE = 6
AND
P.DOCAMNT <> 0
AND P.VOIDED = 0

--and (P.VCHRNMBR = '00000000000001443 ') --or
-- and PA.APTODCNM = '05/09/4596B '

and CASE D.DISTTYPE
WHEN 1 THEN 'Cash'
WHEN 2 THEN 'Payable'
WHEN 3 THEN 'Discount Available'
WHEN 4 THEN 'Discount Taken'
WHEN 5 THEN 'Finance Charge'
WHEN 6 THEN 'Purchase'
WHEN 7 THEN 'Trade Disc.'
WHEN 8 THEN 'Misc. Charge'
WHEN 9 THEN 'Freight'
WHEN 10 THEN 'Taxes'
WHEN 11 THEN 'Writeoffs'
WHEN 12 THEN 'Other'
WHEN 13 THEN 'GST Disc'
WHEN 14 THEN 'PPS Amount'
ELSE ''
END <> 'Purchase'
order by coalesce(PA.APTODCDT,'1/1/1900'),coalesce(PA.APTODCNM,'')



then filter for taxes?

 

[VictoriaYudin]

A number of problems I can see using this:
- Any invoice that is paid by multiple checks will cause duplication of data, so your resulting tax amount may be higher than it really is.
- If you have invoices that are fully applied with a return or credit memo, you will be missing them from your results.
- If you have unpaid invoices, they will not be in your results.
- It is entirely possible for this not to match your GL, as you are only taking a subset of what could be going into the GL.

You might find date ranges where this works, but I would worry about all the exceptions I mentioned above.

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:

http://www.GPReportsViewer.com/gpreports.html

blog:

http://victoriayudin.com

 

[jcw12000]

Do you have something that will work?

 

[jcw12000]

If I could just get a script that has all the TAX GL info with a voucher number as per PM table

 

[VictoriaYudin]

I don't have anything already created that will do all of this. Part of the problem is that it's very difficult to do this 'generically', as it really depends on the individual set of data.

Maybe you can start with this code (from

http://victoriayudin.com/2009/08/11/sql-view-for-all-posted-gl-transactions-in-dynamics-gp/):

SELECT YEAR1 Trx_Year,
TRXDATE Trx_Date,
JRNENTRY Journal_Entry,
ORTRXSRC Originating_TRX_Source,
REFRENCE Reference,
ORMSTRID Originating_Master_ID,
ORMSTRNM Originating_Master_Name,
ORDOCNUM Originating_Doc_Number,
DEBITAMT Debit_Amount,
CRDTAMNT Credit_Amount,
ACTNUMST Account_Number,
ACTDESCR Account_Description,
CURNCYID Currency_ID
FROM
(SELECT ACTINDX, OPENYEAR YEAR1, TRXDATE,
JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM,
DEBITAMT, CRDTAMNT, CURNCYID
FROM GL20000
WHERE SOURCDOC not in ('BBF','P/L')
UNION ALL
SELECT ACTINDX, HSTYEAR YEAR1, TRXDATE,
JRNENTRY, ORTRXSRC, REFRENCE,
ORDOCNUM, ORMSTRID, ORMSTRNM,
DEBITAMT, CRDTAMNT,CURNCYID
FROM GL30000
WHERE SOURCDOC not in ('BBF','P/L')) GL
INNER JOIN GL00105 GM
ON GL.ACTINDX = GM.ACTINDX
INNER JOIN GL00100 GA
ON GL.ACTINDX = GA.ACTINDX

where ACTNUMST = 'YourTaxAccountNumber' --CHANGE THIS TO YOURS

And see what types of transactions you have here based on the first 5 characters of the Originating_TRX_Source.

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:

http://www.GPReportsViewer.com/gpreports.html

blog:

http://victoriayudin.com

 

[jcw12000]

This script will this give me all the GL transaction?

 

[VictoriaYudin]

Correct.

Victoria Yudin
Dynamics GP MVP 2005 - 2012
Use Crystal Reports and SSRS with GP:

http://www.GPReportsViewer.com/gpreports.html

blog:

http://victoriayudin.com