Thanks to Redbeard for giving me the latest switch!
Tuesday, November 7, 2017
Updated Dex.ini Switch Document Uploaded
Thanks to Redbeard for giving me the latest switch!
Tuesday, August 29, 2017
Victoria Does it Again - Unposted Check Report
Our own Victoria Yudin has once again hit a home run with her view revealing unposted checks in Dynamics GP. She has solved the problem of the "... universally hated ..." Check Batch Edit List report. You can read her post, and get her code here: https://victoriayudin.com/2017/08/29/sql-view-for-unposted-checks-in-dynamics-gp/I'm always afraid links will break, so I have copied her post below. Be sure to check her site for any modifications she may make in the future. The little man pictured at the start of this post is from Victoria's Web site, she always has the best graphics
Live the dream!
Thank you Victoria -
SQL view for unposted checks in Dynamics GP
August 29, 2017 
0 Comments
0 Comments I have been asked more than a few times for a script that shows uposted checks and their apply detail. This can be helpful to replace the Check Batch Edit List report, which is universally hated by all GP users I have spoken to.
create view view_unposted_check_batch
as -- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- view_unposted_check_batch
-- Created Aug 29, 2017 by Victoria Yudin, Flexible Solutions
-- For updates see https://victoriayudin.com/gp-reports/
-- Shows details of unposted checks and how they are applied
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ select
rtrim(p.BACHNUMB) Batch,
rtrim(p.CHEKBKID) Checkbook,
rtrim(p.PMNTNMBR) [Payment Number],
p.DOCDATE [Check Date],
rtrim(p.CHEKTOTL) [Check Amount],
rtrim(p.VENDORID) [Vendor ID],
rtrim(p.VENDNAME) [Vendor Name],
rtrim(p.ADDRESS1) Address1,
rtrim(v.PYMTRMID) [Payment Terms],
rtrim(a.DOCNUMBR) [Invoice Number],
a.DOCDATE [Invoice Date],
a.DocDueDate [Due Date],
a.AMNTPAID [Amount Paid],
a.DISTKNAM Discount,
rtrim(a.TRXDSCRN) [Invoice Description]
from PM10300 p
left outer join PM10201 a
on p.PMNTNMBR = a.PMNTNMBR
inner join PM00200 v
on p.VENDORID = v.VENDORID
where p.BCHSOURC like '%check%'
and AMNTPAID <> 0
and CHEKTOTL <> 0
-- add permissions for DYNGRP
go
grant select on view_unposted_check_batch to DYNGRP
Wednesday, August 23, 2017
Additional Dynamics GP Tools from Microsoft- includes 2016
Below you'll find a list of additional tools available from Microsoft that are free for the downloading. This is a copy of the document displayed on Microsoft's web site. You will need access to Customer/PartnerSource in order to download them. These tools solve common aggravations that we have when we need the software to behave just a little bit different than it does. Don't pay for that customization until you check this list. Who knows, it might have been there waiting for you to download it all the while. Please note that there is no guarantee that these tools will be upgraded with new releases. Please read the Support Information section at the bottom.
Download / File type (size) /
Release Date |
Description
|
AutoDim2013.zip ZIP (112KB) 2/27/2013AutoDim2015.zip ZIP (112KB) 3/2/2015AutoDim2016.zip ZIP (112KB) 5/3/2016
|
Allows you to launch Microsoft Dynamics and run
one or more integrations or integration groups and have Microsoft Dynamics GP exit upon completion of the integrations.This tool also supports integrating into several different companies. |
AutoPost2013.zip ZIP (189KB) 2/27/2013AutoPost2015.zip ZIP (189KB) 3/2/2015AutoPost2016.zip ZIP (189KB) 5/3/2016
|
Allows you to call a COM DLL to post Sales Order
Processing, GL Transaction Entry, IV Transaction Entry and RM Cash Receipt Batches. **Requires VB6 runtime to register the dll, not installed by default on a new operating system** |
Calculates YTD & LTD Average Days to Pay for Customers.
Note: The same install can be used for Microsoft Dynamics GP 2010, Microsoft Dynamics GP 2013, Microsoft Dynamics GP 2015, and Microsoft Dynamics GP 2016 | |
DetailPayrollActivityTracking_2013.zip
ZIP (284KB) | 2/27/2013DetailPayrollActivityTracking_2013SP2.zip ZIP (287KB) | 9/18/2013 (or later)DetailPayrollActivityTracking_2015.zip ZIP (284KB) | 1/27/2015DetailedPayrollActivityTracking_2016.zip ZIP (284KB) | 5/3/2016 |
Allows you to track employee changes made in
Microsoft Dynamics GP. |
POPOverReceiptTolerance10.zip
ZIP (244KB) | 3/26/2012POPOverReceiptTolerance2010.zip ZIP (285KB) | 3/26/2012 |
Allows a receipt tolerance percentage for
quantities. A setup window allows the user to enter a tolerance percentage, that percentage is then used when a Purchase Order is received. The highest quantity that can be received is the original quantity ordered plus the percentage amount. NOTE: This tool is included in Microsoft Dynamics GP 2013. |
POReturns2013.zip
ZIP (308KB) | 3/2/2015POReturns2015.zip ZIP (311KB) | 3/2/2015POReturns2016.zip ZIP (311KB) | 5/3/2016 |
When invoicing a receipt in POP, this tool will
check the previous quantity invoiced and the quantity returned for the line item. The maximum quantity allowed to be invoiced is based the following formula: Quantity Shipped minus (Previous Quantity Invoiced plus Quantity Returned). This tool will not allow invoicing of more than this amount. |
RMAutoApply_2013.zip
ZIP (394KB) | 2/27/2013RMAutoApply_2015.zip ZIP (394KB) | 3/2/2015RMAutoApply_2016.zip ZIP (394KB) | 5/3/2016 |
Allows the user to Mass Apply posted RM Credit
Documents to posted RM Debit Documents. The user can select a range of Customer, Debit Document Dates, Credit Document Dates, and a Credit Document Type restriction if desired. |
ShipmentNotification_2013.zip
ZIP (457KB) | 2/27/2013ShipmentNotification_2015.zip ZIP (457KB) | 1/27/2015ShipmentNotification_2016.zip ZIP (457) | 5/3/2016 |
Allows drop ship sales orders to be transferred to
invoice prior to invoicing the purchase order. |
SOPSortLineItems_2013.zip
ZIP (347KB) | 2/27/2013SOPSORTLineItems_2015.zip ZIP (347KB) | 3/2/2015SOPSORTLineItems_2016.zip ZIP (347KB) | 8/31/2016 |
Allows the user to define custom sorting options to display line items in Sales Order Processing.
|
SOPtoPOPLineReorderingUtility_2013.zip ZIP (279KB) | 2/27/2013SOPtoPOPLineReorderingUtility_2015.zip
ZIP (279KB) | 3/2/2015SOPtoPOPLineReorderingUtility_2016.zip ZIP (278KB) | 8/31/2016 |
This tool will reorder the line items of Purchase
Orders generated by the SOP to POP transfer process to be in the same order as they originally were on the Sales Order document(s). |
SOPTaxSchedRolldown_2013.zip
ZIP (63KB) | 8/12/2013SOPTaxSchedRolldown_2015.zip ZIP (66KB) | 3/2/2015SOPTaxSchedRolldown_2016.zip ZIP (64KB) | 8/31/2016 |
This tool allows the Tax Schedule ID in the Sales
Customer Detail Entry window to be used for the Sales document header and line items. |
Allows for absolute values in the Safe Pay file if your bank requires it.
Mariano Gomez explains it as: This chunk file is supposed to add up voids and checks in the summary line of the file by altering the Net Total functionality, which, without the customization, subtracts total voids amount from the total checks amount.
Note: The same install can be used for
Microsoft Dynamics GP 2010, Microsoft Dynamics GP 2013, Microsoft Dynamics GP 2015 and later | |
This tool allows you to change the Contract
Number, Contract ID, Project Number, Project ID and Cost Category ID for Project Accounting |
Tuesday, August 15, 2017
Learning Macros
Today's the day you can start! GPUG is offering a one-hour Webinar Today (Aug 15, 2017) at 2pm CST entitled 'Getting Started with Macros'.
Join in the fun and learn what a macro is and how you can use them.
- Learn how easy macros are to create and to even modify using Notepad.
- Have you ever tried to use a macro to print a report to screen, just to have it shrink up on you?
- Have you ever wanted to copy and paste a value?
- Are you familiar with the concept of nesting macros?
- Would you like to run check links or reconcile overnight?
Leslie
Don't forget 2:00 - 3:00 Central time!
Saturday, April 8, 2017
T-SQL View to create Dynamics GP GL Summary Trial Balance
Do you want to see your trial balance in excel? Do you want the accounts on the left and the years going in columns across the TOP? Of course you do. Now, let's say you do not have access to FRx or SSRS or Management Reporter or JetReports or any other number of products that make this feat easy. You tried to create a SmartList, but the view just isn't set up to make this look like you want it. If you are looking for a straightforward set of views that will give you a simple summary trial balance with as many years as you want, you have come to the right place!
Today, I uploaded a file to BOX that includes the code needed to create the views. The final view is the trail balance that looks like the Excel worksheet below. I linked the view to the spreadsheet so I can have easy access to it whenever someone asks me for such a thing. If you like it, you can get a copy for yourself by downloading the SQL scripts here.
I got a message that some folks weren't allowed to download things at their work, so I'm pasting the statements below my signature.
Live the dream!
Leslie
/* Leslie Vail 3/17/2017------------------------------------------------
This is a series of views that culminate in a final view showing a summary trial balance by fiscal year.
1. The first view creates a unioned summary balance list by account by period by year.
2. The second view consolidates the periods so you have a list of account balances by year.
3. The third view creates the PIVOT showing the account balance by year with the years as columns. The view provides columns for years [1990] through [2020], you'll need to edit it if you have years that fall outside of that range, or if you want to remove some years.
NULLs appear where the account did not exist for that year. If you do not want the NULLs, simply use COALESCE statement.
The only tables included are:
GL00100 Account Master
GL00105 Account Index Master
GL1011O Open Year Summary Master
GL10111 History Year Summary Master
--------------------------------------------------------------*/
/*
FIRST VIEW TO CREATE A UNIONED SUMMARY BALANCE LIST BY
ACCOUNT BY PERIOD BY YEAR --
*/
ALTER VIEW [dbo].[vw1LV_AccountSummaryYears]
AS SELECT rtrim(['Account Current Summary Master'].[YEAR1]) AS 'FiscalYear'
, ['Account Current Summary Master'].[PERIODID] AS 'PeriodID'
, (
SELECT rtrim([ACTNUMST])
FROM [GL00105] AS ['Account Index Master']
WHERE ['Account Index Master'].[ACTINDX] = ['Account Master'].[ACTINDX]
) AS 'AccountNumber'
, rtrim(['Account Master'].[ACTDESCR]) AS 'AccountDescription'
, ['Account Current Summary Master'].[DEBITAMT] AS 'DebitAmount'
, ['Account Current Summary Master'].[CRDTAMNT] AS 'CreditAmount'
, ['Account Current Summary Master'].[PERDBLNC] AS 'PeriodBalance'
, rtrim(['Account Master'].[ACTALIAS]) AS 'AccountAlias'
, 'Account Category Number' = dbo.DYN_FUNC_Account_Category_Number(['Account Current Summary Master'].[ACCATNUM])
, ['Account Current Summary Master'].[ACTINDX] AS 'Account Index'
, 'Account Type' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE])
, 'Active' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ACTIVE])
, 'Adjust for Inflation' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ADJINFL])
, 'Balance For Calculation' = dbo.DYN_FUNC_Balance_For_Calculation(['Account Master'].[BALFRCLC])
, 'Conversion Method' = dbo.DYN_FUNC_Conversion_Method(['Account Master'].[CNVRMTHD])
, ['Account Master'].[CREATDDT] AS 'Created Date'
, 'Decimal Places' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Account Master'].[DECPLACS])
, 'Document Status' = dbo.DYN_FUNC_Document_Status_GL_Sum(1)
, 'Fixed Or Variable' = dbo.DYN_FUNC_Fixed_Or_Variable(['Account Master'].[FXDORVAR])
, ['Account Master'].[HSTRCLRT] AS 'Historical Rate'
, ['Account Master'].[INFLAEQU] AS 'Inflation Equity Account Index'
, ['Account Master'].[INFLAREV] AS 'Inflation Revenue Account Index'
, rtrim(['Account Master'].[MNACSGMT]) AS 'Main Account Segment'
, ['Account Master'].[MODIFDT] AS 'Modified Date'
, ['Account Master'].[NOTEINDX] AS 'Note Index'
, 'Post Inventory In' = dbo.DYN_FUNC_Post_Inventory_In(['Account Master'].[PostIvIn])
, 'Post Payroll In' = dbo.DYN_FUNC_Post_Payroll_In(['Account Master'].[PostPRIn])
, 'Post Purchasing In' = dbo.DYN_FUNC_Post_Purchasing_In(['Account Master'].[PostPurchIn])
, 'Post Sales In' = dbo.DYN_FUNC_Post_Sales_In(['Account Master'].[PostSlsIn])
, 'Posting Type' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP])
, ['Account Master'].[ACTNUMBR_1] AS 'Segment1'
, ['Account Master'].[ACTNUMBR_2] AS 'Segment2'
, ['Account Master'].[ACTNUMBR_3] AS 'Segment3'
, ['Account Master'].[ACTNUMBR_4] AS 'Segment4'
, 'Typical Balance' = dbo.DYN_FUNC_Typical_Balance(['Account Master'].[TPCLBLNC])
, rtrim(['Account Master'].[USERDEF1]) AS 'User Defined 1'
, rtrim(['Account Master'].[USERDEF2]) AS 'User Defined 2'
, '4' AS 'Segments'
, 'Ledger Name' = dbo.DYN_FUNC_GL_Ledger_Name(['Account Current Summary Master'].[Ledger_ID])
, 'Ledger Description' = dbo.DYN_FUNC_GL_Ledger_Description(['Account Current Summary Master'].[Ledger_ID])
, 'Account Index For Drillback' = 'dgpp://DGPB/?Db=&Srv=DALAP06&Cmp=MPI&Prod=0' + dbo.dgppAccountIndex(1, ['Account Current Summary Master'].[ACTINDX])
FROM [GL10110] AS ['Account Current Summary Master'] WITH (NOLOCK)
LEFT JOIN [GL00100] AS ['Account Master'] WITH (NOLOCK)
ON ['Account Current Summary Master'].[ACTINDX] = ['Account Master'].[ACTINDX]
UNION ALL
SELECT rtrim(['Account Summary History'].[YEAR1]) AS 'FiscalYear'
, ['Account Summary History'].[PERIODID] AS 'PeriodID'
, (
SELECT rtrim([ACTNUMST])
FROM [GL00105] AS ['Account Index Master']
WHERE ['Account Index Master'].[ACTINDX] = ['Account Master'].[ACTINDX]
) AS 'AccountNumber'
, rtrim(['Account Master'].[ACTDESCR]) AS 'AccountDescription'
, ['Account Summary History'].[DEBITAMT] AS 'DebitAmount'
, ['Account Summary History'].[CRDTAMNT] AS 'CreditAmount'
, ['Account Summary History'].[PERDBLNC] AS 'Period Balance'
, rtrim(['Account Master'].[ACTALIAS]) AS 'AccountAlias'
, 'Account Category Number' = dbo.DYN_FUNC_Account_Category_Number(['Account Summary History'].[ACCATNUM])
, ['Account Summary History'].[ACTINDX] AS 'Account Index'
, 'Account Type' = dbo.DYN_FUNC_Account_Type(['Account Master'].[ACCTTYPE])
, 'Active' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ACTIVE])
, 'Adjust for Inflation' = dbo.DYN_FUNC_Boolean_All(['Account Master'].[ADJINFL])
, 'Balance For Calculation' = dbo.DYN_FUNC_Balance_For_Calculation(['Account Master'].[BALFRCLC])
, 'Conversion Method' = dbo.DYN_FUNC_Conversion_Method(['Account Master'].[CNVRMTHD])
, ['Account Master'].[CREATDDT] AS 'Created Date'
, 'Decimal Places' = dbo.DYN_FUNC_Decimal_Places_QTYS(['Account Master'].[DECPLACS])
, 'Document Status' = dbo.DYN_FUNC_Document_Status_GL_Sum(2)
, 'Fixed Or Variable' = dbo.DYN_FUNC_Fixed_Or_Variable(['Account Master'].[FXDORVAR])
, ['Account Master'].[HSTRCLRT] AS 'Historical Rate'
, ['Account Master'].[INFLAEQU] AS 'Inflation Equity Account Index'
, ['Account Master'].[INFLAREV] AS 'Inflation Revenue Account Index'
, rtrim(['Account Master'].[MNACSGMT]) AS 'Main Account Segment'
, ['Account Master'].[MODIFDT] AS 'Modified Date'
, ['Account Master'].[NOTEINDX] AS 'Note Index'
, 'Post Inventory In' = dbo.DYN_FUNC_Post_Inventory_In(['Account Master'].[PostIvIn])
, 'Post Payroll In' = dbo.DYN_FUNC_Post_Payroll_In(['Account Master'].[PostPRIn])
, 'Post Purchasing In' = dbo.DYN_FUNC_Post_Purchasing_In(['Account Master'].[PostPurchIn])
, 'Post Sales In' = dbo.DYN_FUNC_Post_Sales_In(['Account Master'].[PostSlsIn])
, 'Posting Type' = dbo.DYN_FUNC_Posting_Type(['Account Master'].[PSTNGTYP])
, ['Account Master'].[ACTNUMBR_1] AS 'Segment1'
, ['Account Master'].[ACTNUMBR_2] AS 'Segment2'
, ['Account Master'].[ACTNUMBR_3] AS 'Segment3'
, ['Account Master'].[ACTNUMBR_4] AS 'Segment4'
, 'Typical Balance' = dbo.DYN_FUNC_Typical_Balance(['Account Master'].[TPCLBLNC])
, rtrim(['Account Master'].[USERDEF1]) AS 'User Defined 1'
, rtrim(['Account Master'].[USERDEF2]) AS 'User Defined 2'
, '4' AS 'Segments'
, 'Ledger Name' = dbo.DYN_FUNC_GL_Ledger_Name(['Account Summary History'].[Ledger_ID])
, 'Ledger Description' = dbo.DYN_FUNC_GL_Ledger_Description(['Account Summary History'].[Ledger_ID])
, 'Account Index For Drillback' = 'dgpp://DGPB/?Db=&Srv=DALAP06&Cmp=MPI&Prod=0' + dbo.dgppAccountIndex(1, ['Account Summary History'].[ACTINDX])
FROM [GL10111] AS ['Account Summary History'] WITH (NOLOCK)
LEFT JOIN [GL00100] AS ['Account Master'] WITH (NOLOCK)
ON ['Account Summary History'].[ACTINDX] = ['Account Master'].[ACTINDX]
GO
GRANT SELECT ON [vw1LV_AccountSummaryYears] TO DYNGRP
GO
/*
SECOND VIEW TO CREATE A SUMMARY BALANCE LIST BY
ACCOUNT BY YEAR
*/
ALTER VIEW [dbo].[vw2LV_ToBuildPivot]
AS
SELECT AccountNumber
, AccountDescription
, FiscalYear
, SUM(PeriodBalance) AS AnnualBalance
FROM vw1LV_AccountSummaryYears
GROUP BY AccountNumber
, AccountDescription
, FiscalYear
GO
GRANT SELECT ON [vw2LV_ToBuildPivot] TO DYNGRP
GO
/*
THIRD VIEW CREATES A PIVOT SHOWING ACCOUNT BALANCE BY YEAR
WITH THE YEARS AS COLUMNS. VIEW INCLUDES COLUMNS FROM 1990 TO 2020.
YOU NEED TO EDIT THE COLUMNS TO INCLUDE ONLY THE ONE'S YOU WANT. YOU CAN ALSO ADD
NEW ONES IF YOU HAVE TRANSACTIONS IN FISCAL YEARS BEYOD THESE DATES.
*/
ALTER VIEW [dbo].[vw3LV_PivotAnnualBalance]
AS
SELECT *
FROM vw2LV_ToBuildPivot
PIVOT(SUM(AnnualBalance) FOR FiscalYear IN ([1990], [1991], [1992], [1993], [1994], [1995]
, [1996], [1997], [1998], [1999], [2000], [2001], [2002], [2003], [2004], [2005], [2006]
, [2007], [2008], [2009], [2010], [2011], [2012], [2013], [2014], [2015], [2016], [2017]
, [2018], [2019], [2020]))
AS PIVOT_BAL;
GO
GRANT SELECT ON [vw1LV_AccountSummaryYears] TO DYNGRP
GO
Subscribe to:
Posts (Atom)