Saturday, December 30, 2017

Making FRx work with unsupported versions of Dynamics

FRX opening screen
I know, I know, we should all be using Management Reporter. News flash! Not everybody wanted to change if I could get FRx to print the reports with their new GP versions. Let’s face it, getting that software installed and getting all of your FRx reports migrated is an expense many people would rather not incur. The secret to getting it up and operational is to find the compatible GPConn.dll you need to make it work. That file, by the way, is located in the following folder: C:\Program Files (x86)\Common Files\microsoft shared\Dexterity\GPConn.dll
The trick is getting the right version of the GPConn.dll. I’ve read that you can run the C:\Program Files (x86)\FRx Software\FRx 6.7\FRxReg67.cmd file and it will create the correct GPConn.dll file. While it does create and register the files, it has not always been the file that has worked for me. It’s certainly worth a try, because it has worked now and again. Another discovery I made the other day is that two workstations running the same operating system needed different GPConn.dll to coax FRx to work.
I’ve collected several different versions of the GPConn.dll file and have posted them to my Box account along with a full install of FRx with SP 9. You'll also find SP12 of FRx available there. If you’re looking for an older version, give one of these files a try. You can access them HERE. Try running the FRxReg67.cmd first, but if that doesn’t work, give these others a try.
The combination that worked on my machine, which is running Windows 10 Pro and GP 2016 R2 build 0641 is this:
  • OS: Windows 10 Pro version 1709, Build 16299.371
  • SQL Driver: SQL Server (not any of the Native Clients)
  • GPConn.dll: version date 04/06/2006 (143 kb)
  • 32-bit ODBC using SQL Native Client 10
Other combinations may very well work and I ask you to please comment and let us know which combinations worked for you.
If you are getting the error 'The current version of the application could not be determined. The setup will now terminate'  You need to go to Control Panel's Programs and Features applet and then, in the upper left-hand corner select Turn Windows Features on or Off and turn on .Net 2.0, which also enables .Net 3.0. I also installed the Prerequisite named 'Microsoft Data Access', but I don't think that was the key to getting it to work.

Live the dream!
Leslie

Thursday, December 21, 2017

It’s Bonus Time!! How Should we Withhold??

happy_money_PA_300_clr_3212Things are going great this year and your company has decided to pay bonuses to all of the employees. YAY!

So, how much should we withhold on the bonus? How should we set up that bonus pay in Dynamics GP?

The first question you need to answer is whether or not the payments are considered Supplemental Wages for Federal tax withholding purposes. Whether wages are classified as regular wages or Supplemental Wages may have significance in determining the amount of income tax required to be withheld. Most bonuses that I’ve seen are in fact Supplemental Wages.

The answer to the withholding question is as close as Internal Revenue Bulletin 2008-24, which outlines nine different scenarios and explains the withholding requirements for each scenario. The nine different scenarios include:

  1. Commissions paid at fixed intervals with no regular wages paid to the employee;
  2. Commissions paid at fixed intervals in addition to regular wages paid at different intervals;
  3. Draws paid in connection with commissions;
  4. Commissions paid to the employee only when the accumulated commission credit of the employee reaches a specific numerical threshold;
  5. A signing bonus paid prior to the commencement of employment;
  6. Severance pay paid after the termination of employment;
  7. Lump sum payments of accumulated annual leave;
  8. Annual payments of vacation and sick leave; and
  9. Sick pay paid at a different rate than regular pay.

Simply defined in IRS Publication 15, “Supplemental Wages are wage payments to an employee that aren't regular wages. They include, but aren't limited to, bonuses, commissions, overtime pay, payments for accumulated sick leave, severance pay, awards, prizes, back pay, retroactive pay increases, and payments for nondeductible moving expenses. Other payments subject to the supplemental wage rules include taxable fringe benefits and expense allowances paid under a non-accountable plan.”

How you withhold on supplemental wages depends on whether the supplemental payment is identified as a separate payment from regular wages. See Regulations section 31.3402(g)-1 for additional guidance.

If you are running a separate check run or separately identify the ‘non-regular’  wages in your records, they are treated as Supplemental Wages. The most obvious would be  a bonus check. The IRS provides that you should withhold at a flat rate of 25% rather than aggregating the bonus with the regular wages.

If the bonus exceeds $1,000,000, you are required to withhold at a rate of 39.7% on the amount in excess of $1,000,000. These amounts and percentages can change every year, you’ll want to check each year and make the appropriate adjustments to your relevant pay codes. The rules are quite complex regarding withholding on Supplemental Wages. The best explanation I’ve found are the scenarios described in Rev. Rul. 2008-29

If you are running a separate check and do not treat it as supplemental wages, the withheld amount will most likely be incorrect. For example, if you run the bonus with an annual frequency, it’s likely no tax would be withheld on a modest bonus. If you include it with regular pay in a semiweekly pay period, it’s likely that too much will be withheld.

FICA/s and FICA/m withholdings are a fixed amount, so those would be calculated the same no matter what kind of wages you're paying.

If you include the bonus with the regular pay, the withholding percentage could be much higher than 25% because of how pay is aggregated. The bonus pay plus the regular pay are added together and withholding is calculated on the annualized sum of the two.

This article speaks only to Federal tax withholding, you'll need to check your state withholding rules on supplemental wages to see how the state requires you to withhold.

Setting it up in Dynamics GP

For Dynamics GP, here’s how you would set up a Bonus pay type using flat rate withholding.

pay code bonus setup annual fixed %

I have three elements highlighted on the screenshot above.

  1. Pay Type
  2. Pay Period
  3. Flat Tax Rate

I use the Pay Type of ‘Other’ because a bonus doesn’t fit into any of the other categories

I use the Pay Period of annually because this is a bonus given just once per year.  If they get a bonus quarterly, then select a Pay Period of Quarterly.

The Flat Tax Rates section is where you would enter the pre-defined percentage for withholding against the bonus amount. This flat tax does not apply to any other pay codes

What difference does it make?.

This section will show you the amounts withheld for Federal Income Tax under several scenarios.

The following calculations are based on these facts: The bonus is $1,000 and it is a year-end bonus. . I used Fabrikam’s Pillar Ackerman for my test subject. She has $122.95 of ‘before tax’ deductions so I applied the tax rates to $877.05 ($1,000 – $122.95). This explains why the 25% flat tax on the bonus was less than $250.

1. Bonus Pay Period is Daily/Misc. Bonus is Included with regular pay:

FIT Withheld on bonus = $375.19

2. Bonus Pay Period is Annual. Bonus is included with regular pay:

FIT Withheld on bonus = $35.76

3. Bonus Pay Period is Daily/Misc. Bonus is a separate check run.

FIT Withheld on bonus = $190.57

4. Bonus Pay Period is Annual. Bonus is a separate check run.

FIT Withheld on bonus = $0

5. Bonus Pay Period is Daily/Misc. Flat Tax Rate is 25%. Bonus is a separate check run.

FIT Withheld on bonus = $219.26

6. Bonus Pay Period is Annual. Flat Tax Rate is 25%. Bonus is a separate check run.

FIT Withheld on bonus = $219.26

As you can see, the amount of tax withheld can vary substantially depending on how you have set up the Pay Code for the Bonus.

How do I get the bonus check to equal a set amount?

Many employers like the bonus check to be devoid of withholding amounts. One approach to this is to take the withholding amount from the regular check. This normally causes the employee be , um, dissatisfied. The answer to this conundrum is the gross-up.

Employers would have to pay a larger gross amount so that after the required withholding is deducted, the desired net amount is achieved. Dynamics GP can perform this calculation for you.

To access the Gross Up utility, navigate to:

HR & Payroll | Utilities | Payroll | Gross Up

Gross Up in Payroll

Put the net check amount you want, and then adjust the percentages for tax withholding as appropriate. Click on the Calculate button and the system will provide the Gross Amount in order to arrive at the desired check amount. Pretty neat.

I hope this article will give you some food for thought on how to handle the withholding on bonus checks and other supplemental wages.

Live the dream!

Leslie

Tuesday, November 7, 2017

Updated Dex.ini Switch Document Uploaded


I've finally updated the Dex.ini switch document on Box. If you want a copy, please download it here:  https://app.box.com/s/xkgae2rz1vil1ap4o579
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


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.

Do​wnload / 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**
AverageDaytoPay.zip     ZIP (98KB)  2/24/2016
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/2013
DetailPayrollActivityTracking_2013SP2.zip ZIP (287KB) | 9/18/2013 (or later)DetailPayrollActivityTracking_2015.zip
ZIP (284KB) | 1/27/2015
DetailedPayrollActivityTracking_2016.zip
ZIP (284KB) | 5/3/2016
Allows you to track employee changes made in
Microsoft Dynamics GP.
 
 
         Previous Version Installs
 
 
POPOverReceiptTolerance10.zip
ZIP (244KB) | 3/26/2012
POPOverReceiptTolerance2010.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/2015
POReturns2015.zip
ZIP (311KB) | 3/2/2015
POReturns2016.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/2013
RMAutoApply_2015.zip
ZIP (394KB) |  3/2/2015
RMAutoApply_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/2013
ShipmentNotification_2015.zip
ZIP (457KB) | 1/27/2015
ShipmentNotification_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/2013
SOPSORTLineItems_2015.zip
ZIP (347KB)  | 3/2/2015
SOPSORTLineItems_2016.zip
ZIP (347KB) |  8/31/2016
Allows the user to define custom sorting options to display line items in Sales Order Processing.
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/2013
SOPTaxSchedRolldown_2015.zip
ZIP (66KB) | 3/2/2015
SOPTaxSchedRolldown_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.
SpTotals.zip   ZIP (63KB) | 6/4/2015
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
Changer Scripts 10.0
ZIP (6KB) | 4/22/2013
Changer Scripts 2010
ZIP (4KB) | 4/22/2013
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

Interested in getting started with 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?
Sign up for the Webinar today and I will show you some macro tips that will put you on the path of becoming a macro superstar.

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