SQL view for Payables invoices originating from POP in Dynamics GP

September 1, 2010 by Victoria Yudin 7 Comments

This Dynamics GP SQL view originated from a request on the Dynamics GP customer forum, but is also something that I can see being useful in a variety of situations. It returns all posted Payables invoices that came from the Purchase Order Processing module with details of the items that were received on each invoice.

~~~~~

CREATE VIEW view_Payables_POP_Invoices
AS
/*******************************************************************
view_Payables_POP_Invoices
Created on Sep 1, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates visit http://victoriayudin.com/gp-reports/
Returns Payables Invoices only
*******************************************************************/

SELECT	PM.VCHRNMBR Voucher_Number,
	PM.VENDORID Vendor_ID,
	POP.VENDNAME Vendor_Name,
	PM.DOCDATE Invoice_Date,
	PM.DUEDATE Due_Date,
	PM.DOCNUMBR Invoice_Number,
	PM.DOCAMNT Invoice_Amount,
	PM.CURTRXAM Unpaid_Amount,
	CASE PM.VOIDED
		WHEN 0 THEN 'No'
		WHEN 1 THEN 'Yes'
		END Voided,
	POP.POPRCTNM POP_Receipt_Number,
	CASE POP.POPTYPE
		WHEN 2 THEN 'Invoice'
		WHEN 3 THEN 'Shipment/Invoice'
		END Receipt_Type,
	POP.receiptdate Receipt_Date,
	I.RCPTLNNM Receipt_Line_Num,
	I.PONUMBER PO_Number,
	I.ITEMNMBR Item_Number,
	I.ITEMDESC Item_Description,
	R.QTYINVCD Quantity_Invoiced,
	R.UOFM U_of_M,
	I.UNITCOST Unit_Cost,
	I.EXTDCOST Extended_Cost,
	I.LOCNCODE Site_ID,
	CASE I.NONINVEN
		WHEN 0 THEN 'Inventory Item'
		WHEN 1 THEN 'Non-Inventory Item'
		END Item_Type

FROM
	(SELECT VCHRNMBR,VENDORID, DOCDATE,
		DUEDATE, DOCNUMBR, DOCAMNT,
		CURTRXAM, DOCTYPE, VOIDED
	 FROM PM20000
	 UNION ALL
	 SELECT VCHRNMBR,VENDORID, DOCDATE,
		DUEDATE, DOCNUMBR, DOCAMNT,
		CURTRXAM, DOCTYPE, VOIDED
	 FROM PM30200) PM  -- PM trx

INNER JOIN POP30300 POP  -- POP header
	ON PM.VENDORID = POP.VENDORID
	AND PM.DOCNUMBR = POP.VNDDOCNM

INNER JOIN POP30310 I  -- POP detail
	ON I.POPRCTNM = POP.POPRCTNM

INNER JOIN POP10500 R  -- recceipt details
	ON R.POPRCTNM = I.POPRCTNM
	AND R.RCPTLNNM = I.RCPTLNNM

WHERE PM.DOCTYPE = 1  -- invoices only
AND POP.POPTYPE in (2,3)  -- invoices only

/** 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_POP_Invoices TO DYNGRP

~~~~~

For more Dynamics GP SQL scripts take a look at the GP Reports page on this blog.

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.

Filed under Dynamics GP, GP Reports code, GP SQL scripts, POP SQL code, Payables SQL code Tagged with , , , ,

5 questions to answer when writing a report spec

August 31, 2010 by Victoria Yudin Leave a Comment


Are you a Dynamics GP user who needs to request a report? Or a consultant working on a report specification for Dynamics GP data? Anyone that needs or creates reports for Dynamics GP can benefit by knowing the Five Questions You Need to Answer When Creating a Microsoft Dynamics GP Report Specification.

Filed under Dynamics GP Tagged with ,

SQL view for asset account numbers in Dynamics GP

August 10, 2010 by Victoria Yudin 1 Comment

Want to see all the account numbers set up for all your Fixed Assets? You can do this in SmartList, but SmartList is sometimes limiting when you want to do complex searches. Here is a view that will return all the account numbers assigned for each asset in Dynamics GP.

~~~~~

CREATE VIEW view_Asset_Accounts
AS

/*******************************************************************
view_Asset_Accounts
Created on Aug 10, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates visit http://victoriayudin.com/gp-reports/
*******************************************************************/

select	F.ASSETID Asset_ID,
		F.ASSETIDSUF Suffix,
		F.ASSETDESC Desciption,
		F.ASSETCLASSID Asset_Class,
		F.Physical_Location_ID,
		G1.ACTNUMST Depreciation_Expense,
		G2.ACTNUMST Depreciation_Reserve,
		G3.ACTNUMST Prior_Yr_Depreciation,
		G4.ACTNUMST Asset_Cost,
		G5.ACTNUMST Proceeds,
		G6.ACTNUMST Recognized_Gain_Loss,
		G7.ACTNUMST Non_Recognized_Gain_Loss,
		G8.ACTNUMST Clearing_Account

from FA00100 F
left outer join FA00400 A
	on F.ASSETINDEX = A.ASSETINDEX
left outer join GL00105 G1
	on A.DEPREXPACCTINDX = G1.ACTINDX
left outer join GL00105 G2
	on A.DEPRRESVACCTINDX = G2.ACTINDX
left outer join GL00105 G3
	on A.PRIORYRDEPRACCTINDX = G3.ACTINDX
left outer join GL00105 G4
	on A.ASSETCOSTACCTINDX = G4.ACTINDX
left outer join GL00105 G5
	on A.PROCEEDSACCTINDX = G5.ACTINDX
left outer join GL00105 G6
	on A.RECGAINLOSSACCTINDX = G6.ACTINDX
left outer join GL00105 G7
	on A.NONRECGAINLOSSACCTINDX = G7.ACTINDX
left outer join GL00105 G8
	on A.CLEARINGACCTINDX = G8.ACTINDX		

/** 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_Asset_Accounts TO DYNGRP

~~~~~

For more Dynamics GP SQL code take a look at the GP Reports page on this blog.

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.

Filed under Dynamics GP, Fixed Assets SQL code, GP Reports code, GP SQL scripts Tagged with , , ,

SQL view for Inventory Price Levels in Dynamics GP

August 9, 2010 by Victoria Yudin 4 Comments

When you are creating prices for different price levels in Dynamics GP, there is no easy way to see these, or to see all the prices for a particular price level. Below is a view that can help with this.

~~~~~

CREATE VIEW view_Inventory_Price_Levels
AS

/*******************************************************************
view_Inventory_Price_Levels
Created on Aug 9, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates visit http://victoriayudin.com/gp-reports/
*******************************************************************/

SELECT IV.ITEMNMBR Item_Number,
       IM.ITEMDESC Item_Description,
       IM.ITMCLSCD Item_Class,
       IV.PRCLEVEL Price_Level,
       CASE IM.PRICMTHD
       	  WHEN 1 THEN 'Currency Amount'
       	  WHEN 2 THEN '% of List Price'
	  WHEN 3 THEN '% Markup – Current Cost'
	  WHEN 4 THEN '% Markup – Standard Cost'
	  WHEN 5 THEN '% Margin – Current Cost'
	  WHEN 6 THEN '% Margin – Standard Cost'
	  END Price_Method,
       IV.CURNCYID Currency_ID,
       IV.UOFM U_of_M,
       CASE IM.PRICMTHD
	  WHEN 1 THEN IV.UOMPRICE
	  WHEN 2 THEN IV.UOMPRICE * IC.LISTPRCE / 100
	  ELSE 0
	  END Price,
       CASE IM.PRICMTHD
	  WHEN 1 THEN 0
	  ELSE IV.UOMPRICE
	  END Percent_of_List,
       IV.FROMQTY From_Qty,
       IV.TOQTY To_Qty,
       IV.QTYBSUOM Qty_In_Base_UofM

FROM   IV00108 IV

LEFT OUTER JOIN
       IV00101 IM
       ON IM.ITEMNMBR = IV.ITEMNMBR

LEFT OUTER JOIN
       IV00105 IC
       ON IC.ITEMNMBR = IV.ITEMNMBR
       AND IV.CURNCYID = IC.CURNCYID

/** 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_Inventory_Price_Levels TO DYNGRP

~~~~~

If you are looking to see customer specific pricing, take a look at my Customer Pricing post.  You can also see a full list of my Dynamics GP SQL code on the GP Reports page of this blog.

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.

Filed under Dynamics GP, GP Reports code, GP SQL scripts, Inventory SQL code Tagged with , , ,

New resource to find Dynamics GP table and field information

July 23, 2010 by Victoria Yudin 4 Comments

Since I have posted a lot of Dynamics GP table information and SQL code, I often get asked the question “what table holds ____ “? Sometimes I just know the answer off the top of my head (yeah, I think that’s scary, too), but once in a while I have to look it up. I have various ways of doing this, sometimes it involves glancing through the list of tables, other times I have used the Support Debugging Tool created by David Musgrave. 

Now there is another great resource for this provided to us again by David Musgrave. You can read all about it in David’s post on Getting Table and Field Data out of Dexterity Dictionaries, however I thought I would take you through an example of how to use this. This example assumes that you have already installed the Support Debugging Tool - if you have not, that’s your first step. Now to our example (I am using GP 2010 for this, but it should work the same for GP 10.0):

  • Log into GP as ‘sa’. (Yes, there are workarounds to allow not having to use ‘sa’, but that’s not what this post is about.)
  • Open the Support Debugging Tool by clicking Ctrl+D or navigating to GP > Tools > Support Debugging Tool.
  • If you have not done so yet, turn on the Advanced Mode features by going to Options > Dex.ini Settings and checking the box for Enable Debugger Advanced Mode Features towards the bottom of the window:
  • Click OK to get back to the main Support Debugging Tool window.
  • Go to Options > Runtime Execute to open the Runtime Execute window. (If you have a System Password setup, and everyone should, you will need to type it in.)
  • Copy and paste the code from David’s post and change the Product to Microsoft Dynamics GP:
  • Click Execute in the bottom right corner.
  • You will get a pop up window letting you know that two files called TABLES.TXT and FIELDS.TXT are about to be created in your GP\Data folder. Click Yes to continue. Once done you will get a message letting you know how many tables and fields were exported:
  • If you want to save this code for use in the future, enter a Script ID and Script Name at the top of the Runtime Execute window and click Save. Otherwise, you can just close this window and choose to Discard your changes.
  • Now you have two text files with your data. The easiest thing to do is open Excel and open one of the files, this will automatically open the Text Import Wizard. In my experience you can just click Finish on the very first step and Excel will do the rest.
  • Repeat the process for the second file, save them in Excel format and now you have searchable files of the tables and fields for your GP installation readily available.
  • David has provided samples of the TABLES.TXT and FIELDS.TXT files in his post so that you can take a look at what kind of data you’d be getting. One huge benefit of going through the steps above instead of just using the samples provided is that any ISV products you have installed will be included when you run this on your system.

I would like to thank David for making this available to all of us!

Filed under Dynamics GP Tagged with , ,

← Older posts