SQL view for Payables invoices originating from POP in Dynamics GP

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.

SQL view for asset account numbers in Dynamics GP

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.

SQL view for Inventory Price Levels in Dynamics GP

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.

SQL view to show yearly totals for Dynamics GP Vendors

Below is a view that will show yearly totals for your Dynamics GP Vendors. It’s something we have used internally for a while, but I have recently gotten a few requests for it, so I cleaned it up and am sharing it. 

This will show yearly totals for calendar years. If you want to use fiscal years instead, change the WHERE clause at the bottom to the following:
WHERE VT.HISTTYPE = 1

 

CREATE VIEW view_Vendor_Yearly_Totals
AS

/***************************************************************
view_Vendor_Yearly_Totals
Shows totals for all AP vendors
Created Jul 15, 2010 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Results shown for calendar months and functionaly currency.
***************************************************************/

SELECT VT.VENDORID Vendor_ID,
       VM.VENDNAME Vendor_Name,
       VM.VNDCLSID Class_ID,
       case VM.VENDSTTS
          when 1 then 'Active'
          when 2 then 'Inactive'
          when 3 then 'Temporary'
          end Vendor_Status,
       case VM.TEN99TYPE
          when 1 then 'Not a 1099 Vendor'
          when 2 then 'Dividend'
          when 3 then 'Interest'
          when 4 then 'Miscellaneous'
          end [1099_Type],
       VM.PYMTRMID Payment_Terms_ID,
       VT.YEAR1 [Year],
       sum(VT.AMBLDLIF) Amount_Billed,
       sum(VT.AMTPDLIF) Amount_Paid,
       sum(VT.TEN99ALIF) [1099_Amount],
       sum(VT.FINCHLIF) Finance_Charges,
       sum(VT.WROFSLIF) Writeoffs,
       sum(VT.RTRNSLIF) [Returns],
       sum(VT.TRDTKLIF) Trade_Discounts,
       sum(VT.DISAVLIF) Term_Discounts_Avail,
       sum(VT.DISTKNLF) Term_Discounts_Taken,
       sum(VT.DISLSTLF) Term_Discounts_Lost,
       sum(VT.Withholding_LIFE) Withholding,
       sum(VT.NOINVLIF) Num_Of_Invoices,
       sum(VT.NFNCHLIF) Num_Of_Finance_Charges,
       VM.ADDRESS1 Address_1,
       VM.ADDRESS2 Address_2,
       VM.ADDRESS3 Address_3,
       VM.CITY City,
       VM.[STATE] [State],
       VM.ZIPCODE Zip_Code,
       VM.COUNTRY Country,
       VM.TXIDNMBR Tax_ID

FROM PM00202 VT

INNER JOIN PM00200 VM
       ON VT.VENDORID = VM.VENDORID

WHERE VT.HISTTYPE = 0

GROUP BY VT.VENDORID, VM.VENDNAME, VM.VNDCLSID,
         VM.VENDSTTS, VM.TEN99TYPE, VM.PYMTRMID,
         VT.YEAR1, VM.ADDRESS1, VM.ADDRESS2,
         VM.ADDRESS3, VM.CITY, VM.[STATE],
         VM.ZIPCODE, VM.COUNTRY, VM.TXIDNMBR

/** 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_Vendor_Yearly_Totals 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.

Updates to SQL view to show all GL distributions for AP transactions

I have made a number of updates to my SQL view to show all GL distributions for AP transactions since I first published it. Some of these were in response to comments asking for additional fields, others were added when I came across new data to test with. Rather than publish another post with the latest revisions, I have updated the code in my original blog post, but I wanted to let everyone know it’s there. Some of the updates are:

  • Added Due To and Due From distribution types for anyone using Intercompany transactions.
  • Added Realized Gain and Realized Loss distribution types for Multicurrency transactions.
  • Added Currency ID, Exchange Rate and Originating Debit/Credit fields.
  • Added Distribution Reference, Batch ID and Transaction Description fields.
  • Added Voucher Number.

As always, if you find any issues or would like any additional updates, please let me know.

There are also enough Payables SQL scripts that I have moved them to their own page. The page can also be accessed from the main navigation menu on this blog under GP Reports and on my GP Reports page.