## TAX Details for invoices PM

## TAX Details for invoices PM

(OP)

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

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

## RE: TAX Details for invoices PM

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

## RE: TAX Details for invoices PM

## RE: TAX Details for invoices PM

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

## RE: TAX Details for invoices PM

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:

Victoria Yudin

Dynamics GP MVP 2005 - 2012

Use Crystal Reports and SSRS with GP:

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

blog: http://victoriayudin.com

## RE: TAX Details for invoices PM

## RE: TAX Details for invoices PM

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

## RE: TAX Details for invoices PM

----------------------------------------------------------

----------------------------------------------------------

----- 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?

## RE: TAX Details for invoices PM

- 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.

Dynamics GP MVP 2005 - 2012

Use Crystal Reports and SSRS with GP:

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

blog: http://victoriayudin.com

## RE: TAX Details for invoices PM

## RE: TAX Details for invoices PM

## RE: TAX Details for invoices PM

Maybe you can start with this code (from h

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.

Dynamics GP MVP 2005 - 2012

Use Crystal Reports and SSRS with GP:

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

blog: http://victoriayudin.com

## RE: TAX Details for invoices PM

## RE: TAX Details for invoices PM

Dynamics GP MVP 2005 - 2012

Use Crystal Reports and SSRS with GP:

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

blog: http://victoriayudin.com