Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

jcw12000 (TechnicalUser) (OP)
19 Apr 12 4:48
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 (IS/IT--Management)
19 Apr 12 8:35
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 (TechnicalUser) (OP)
19 Apr 12 8:47
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 (TechnicalUser) (OP)
19 Apr 12 8:50
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 (IS/IT--Management)
19 Apr 12 9:54
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 (TechnicalUser) (OP)
19 Apr 12 10:07
Thats where I dont seem to find the link from the GL to get the invoice numbers
VictoriaYudin (IS/IT--Management)
19 Apr 12 10:19
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 (TechnicalUser) (OP)
20 Apr 12 3:53
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 (IS/IT--Management)
20 Apr 12 4:24
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 (TechnicalUser) (OP)
20 Apr 12 4:56
Do you have something that will work?
jcw12000 (TechnicalUser) (OP)
20 Apr 12 4:58
If I could just get a script that has all the TAX GL info with a voucher number as per PM table
VictoriaYudin (IS/IT--Management)
20 Apr 12 5:06
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 (TechnicalUser) (OP)
20 Apr 12 5:16
This script will this give me all the GL transaction?
VictoriaYudin (IS/IT--Management)
20 Apr 12 5:23
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

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close