Feeds:
Posts
Comments

SQL view for Payables payment apply detail in Dynamics GP

October 22, 2009 by Victoria Yudin

I have published SQL views that return all posted Payables payments in Dynamics GP and Payables apply information to help identify what payments (or credits) paid off an invoice, however another frequent request is for a list of payments and their apply information.  Below is a view that returns a list of all posted payments with details on how they were applied.  Any payment that was applied to more than one transaction will show up as multiple lines.

For other SQL views on GP data, please visit my GP Reports page.

~~~~~

CREATE VIEW view_Payables_Apply_Detail
AS

/*******************************************************************
view_Payables_Apply_Detail
Created on Oct 22 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Does not take Multicurrency into account.
Will return multiple lines for payments that were applied to
     more than one transaction.
Tables Used:
     PM20000 – Open/Posted Transactions
     PM30200 – Historical/Paid Transactions
     PM00200 – Vendor Master
     PM10200 – Apply To Work/Open
     PM30300 – Apply To History
*******************************************************************/

SELECT	P.VENDORID Vendor_ID,
	V.VENDNAME Vendor_Name,
	V.VNDCHKNM Vendor_Check_Name,
	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

FROM
     (SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
	    DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
	    PYENTTYP, CARDNAME
      FROM PM30200
          UNION ALL
      SELECT 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

WHERE P.DOCTYPE = 6 AND P.VOIDED = 0

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Payables_Apply_Detail TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone

Posted in Crystal Reports, Dynamics GP, GP Reports, GP SQL scripts, SmartList Builder | Tagged , , , , , , | 3 Comments »

Microsoft Dynamics Salary Survey 2009

October 13, 2009 by Victoria Yudin

dollar chrome symbolNigel Frank International is conducting the 2009 annual survey of Microsoft Dynamics salaries worldwide. Salaries are frequently a topic of conversation for many of us, whether we are hiring or looking to be hired, so I encourage everyone to participate in this survey. Better hurry, though, the survey will be closing middle of next week.

As a thank you all participants will receive a PDF report of the results once they have been compiled. This will give you an insight into the salaries, opinions and demographics of your Microsoft Dynamics counterparts worldwide.

Posted in Miscellaneous | Tagged | 1 Comment »

SQL view for all posted Payables transactions in Dynamics GP

October 12, 2009 by Victoria Yudin

Payables Transactions have been a popular request for reports lately. Below is a view that lists all posted Payables transactions in functional currency. Voided transactions are not excluded.

For other SQL views on Dynamics GP data, please visit my GP Reports page.

~~~~~

CREATE VIEW view_PM_Transactions
AS

/***************************************************************
view_PM_Transactions
Created Oct 12 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Shows all posted Payables transactions with Functional amounts.
Does not exclude voided transactions.
Tables used:
	PM00200 - Vendor Master
	PM20000 - Open/Posted Transactions
	PM30200 - Historical/Paid Transactions
***************************************************************/

SELECT	P.VENDORID Vendor_ID,
	V.VENDNAME Vendor_Name,
	P.VCHRNMBR Voucher,
	CASE P.DOCTYPE
	     WHEN 1 THEN 'Invoice'
	     WHEN 2 THEN 'Finance Charge'
     	     WHEN 3 THEN 'Misc Charge'
     	     WHEN 4 THEN 'Return'
     	     WHEN 5 THEN 'Credit Memo'
	     WHEN 6 THEN 'Payment'
	     END Document_Type,
	P.DOCDATE Document_Date,
	P.PSTGDATE GL_Posting_Date,
	P.DUEDATE Due_Date,
	P.DOCNUMBR Document_Number,
	P.DOCAMNT Document_Amount,
	P.CURTRXAM Unapplied_Amount,
	P.TRXDSCRN [Description],
	CASE P.VOIDED
	     WHEN 0 THEN 'No'
	     WHEN 1 THEN 'Yes'
	     END Voided

FROM	(SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
	 DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
	 FROM PM20000
	UNION ALL
	 SELECT VENDORID, VCHRNMBR, DOCTYPE, DOCDATE, PSTGDATE,
	 DUEDATE, DOCNUMBR, DOCAMNT, CURTRXAM, TRXDSCRN, VOIDED
	 FROM PM30200) P

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

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_PM_Transactions TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone

Posted in Dynamics GP, GP Reports, GP SQL scripts | Tagged , , , , , , | 3 Comments »

History of Dynamics GP

September 30, 2009 by Victoria Yudin

Do you still call it Great Plains because you’re so used to it? Or is Dynamics GP the only name you’ve ever used? In either case, you may find looking over the timeline of Dynamics GP that DynamicsWorld UK put together very educational and interesting. I have been working with GP as a customer since the DOS days and as a consultant for over 10 years, but I still learned a lot from reading this.

DynamicsWorld UK also has timelines of the other Dynamics products as well as weekly interviews with Dynamics MVPs. I even managed to sneak an interview in there on September 19th. :-)

Posted in Dynamics GP | Tagged , | 2 Comments »

SQL view for all posted Payables payments in Dynamics GP

September 11, 2009 by Victoria Yudin

This view is in response to more than a few inquiries I have had for a vendor check report. This view brings in all payments, not just checks, so if only checks are needed, add the following to the end of the WHERE clause:

AND P.PYENTTYP = 0

For other SQL code, please visit my GP Reports page.

~~~~~

CREATE VIEW view_Payables_Payments
AS

/****************************************************************
view_Payables_Payments
Created Sep 11, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Shows all posted Payables payments in Functional Currency only
Tables used:
     PM00200 - Vendor Master
     PM20000 – Open Transactions
     PM30200 - Historical Transactions
****************************************************************/

SELECT	P.VENDORID Vendor_ID,
	V.VENDNAME Vendor_Name,
	V.VNDCHKNM Vendor_Check_Name,
	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 Document_Date,
	P.PSTGDATE GL_Posting_Date,
	P.VCHRNMBR Voucher_Number,
	P.DOCNUMBR Document_Number,
	P.DOCAMNT Functional_Amount,
	P.TRXSORCE Transaction_Source

FROM
     (SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
	    DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
	    PYENTTYP, CARDNAME
      FROM PM30200
          UNION ALL
      SELECT VENDORID, DOCTYPE, DOCDATE, VCHRNMBR, DOCNUMBR,
	    DOCAMNT, VOIDED, TRXSORCE, CHEKBKID, PSTGDATE,
	    PYENTTYP, CARDNAME
      FROM PM20000) P

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

WHERE P.DOCTYPE = 6 AND P.VOIDED = 0 

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Payables_Payments TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone

Posted in Crystal Reports, Dynamics GP, GP Reports, GP SQL scripts | Tagged , , , , , , | 4 Comments »