Richard Whaley has passed away; 7/1/51- 7/1/2014

Farewell to the Professor. It saddens me to tell you that we lost Richard Whaley this morning 07/01/2014. He was home with his family by his side. As you may know, Richard’s health had been very bad these last couple of years, he slipped away in his sleep.

clip_image002

Richard was a very close friend of mine and he will be sorely missed. I was very lucky to spend a few days with him the week before last, and I will treasure that trip for a lifetime.

Richard was one of the GPUG Superstars, on the ‘top’ 100 list from forever, and a former Microsoft GP MVP. We remember him for his conference antics, like cruising around on a Segway and dressing up like a professor.

Richard brought us nearly two dozen books on Dynamics GP, most of which he penned himself, as well as books on AX, CRM, NAV, SL and SQL Reporting Services:

· Dynamics GP

· Dynamics AX

· Dynamics CRM

· Dynamics NAV

· Dynamics SL

· MS SQL Reporting Svcs

As well as being an expert on the Manufacturing module, he was versed in nearly every other module. He knew so much about Dynamics GP and was never too busy to help others. Richard answered hundreds of questions for us on the Community Forum, he gave countless hours of his time to GPUG and taught classes at most of the conferences.

He will be a hard act to follow.

Please send me any pictures you may have of Richard and I will add them to this post.

Kind regards,

Leslie

Read More:
Richard Whaley has passed away; 7/1/51- 7/1/2014

July 1, 2014 · Leslie Vail · No Comments
Tags: , , , , , , ,  · Posted in: Blogs I Follow Total Views: 331

GP 2013 R2 updates to Report Writer Functions

David Musgrave posted an article about updates and additions he has made to the User-Defined functions he added to Report Writer back in release 7.0. I have added a list and description of those functions to my section about User-Defined Report Writer functions.
There are some incredibly handy functions now available. Check out his post by following this link.
David Musgrave’s updates to Report Writer functions
I have copied his entire article below:

By David Musgrave, View Profile8 Jun 2014 6:00 PM You might remember the story about how I created a suite of Report Writer functions and got them added to the version 7.0 Dynamics.dic core dictionary. The aim for these functions was to make it easier for partner consultants and customers to modify the reports without needing custom scripting in Visual Basic for Applications (VBA) or Dexterity. They are documented in the Report Writer Functions document in the Software Developers Kit (SDK).
Based on feedback I have received from the community, I have made some changes to a few of the Report writer functions and submitted them to the development team for inclusion in the Microsoft Dynamics GP 2013 R2 (Service Pack 3) code. Below is a summary of the Report Writer (RW) functions changed and what new functionality is available. RW_CoAddrIDInfo()

  • If an empty string is passed as the Address ID (first) parameter, the address data is pulled from the Company Master (SY_Company_MSTR, SY01500) table instead of Location Master (SY_Location_MSTR, SY00600) table.

RW_PMAddrIDInfo()

  • If an empty string is passed as the Address ID (second) parameter, the address data is pulled from the Vendor Master (PM_Vendor_MSTR, PM00200) table instead of Vendor Address Master (PM_Address_MSTR, PM00300) table.

RW_RMAddrIDInfo()

  • If an empty string is passed as the Address ID (second) parameter, the address data is pulled from the Customer Master (RM_Customer_MSTR, RM00101) table instead of Customer Address Master (RM_Customer_MSTR_ADDR, RM00102) table.
  • For the Field (third) parameter, The values 14 and 15 have been added for the User Defined 1 and User Defined 2 fields respectively.

RW_GetInternetText()

  • Updated Field (sixth) parameter, so that it works without errors. The values of 10 (to), 11(cc) and 12(bcc) return email addresses, any other value returns the internet text field.

RW_ConvertToWordsAndNumbers()

  • Added new mode to support full conversion of dollars and cents to words. The Mode (third) parameter now supports the following three values:

0 = Whole Number in words, Parts in numbers
1 = Whole Number in numbers, Parts in numbers
2 = Whole Number in words, Parts in words *NEW
RW_ConvertToWordsAndNumbersParse()

  • Added new mode to support full conversion of dollars and cents to words. The Mode (third) parameter now supports the following three values:

0 = Whole Number in words, Parts in numbers
1 = Whole Number in numbers, Parts in numbers
2 = Whole Number in words, Parts in words *NEW
Here are some examples of the RW_ConvertToWordsAndNumbers() function in action:

  • RW_ConvertToWordsAndNumbers( 1234.56   “”   0 )

One Thousand Two Hundred Thirty Four Dollars and 56 Cents

  • RW_ConvertToWordsAndNumbers( 1234.56   “”   1 ) returns

** 1,234 Dollars and 56 Cents

  • RW_ConvertToWordsAndNumbers( 1234.56   “”   2 ) returns

One Thousand Two Hundred Thirty Four Dollars and Fifty Six Cents (the above function is incredible; it’s one we’ve wanted for years!) For more information, check out the following blog articles:

Hope you like the improvements.” Enjoy! Leslie

Follow this link:
GP 2013 R2 updates to Report Writer Functions

June 10, 2014 · Leslie Vail · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 374

SQL View Union of all Payroll Codes

I just uploaded a file to GPUG ‘Share my Code’ to create a view that combines the Pay Code, Benefit and Deduction code tables into a single view. This can be used to create a great SmartList or Excel report so that you can view all of the current amounts for each employee from a single list.

Tables included: UPR00400 (Pay Code) UPR00500 (Deduction Code) UPR00600 (Benefit Code) UPR00100 (Employee Master). The following fields are included:

Employee_ID Last_Name First_Name Middle_Name Code Code_Type Pay_Type Inactive Begin_Date End_Date Base_Pay_Record Amount Report_As_Wages Subj_to_FedTax Subj_to_FICA Subj_to_Medicare Subj_to_SateTax Subj_to_LocalTax Subj_to_FUTA Subj_to_SUTA SUTA_State Flat_FedTax_Rate Flat_StateTax_Rate Variable Frequency Taxable Based_on_Records Based_on_Rate Method Formula Percent Tier_Max Tier_Max_Units Max_per_Pay_Period Max_per_Year Lifetime_Max W2_Box_Number W2_Box_Label Note_Index Note_Index2 Life_to_Date_Amt Data_Entry_Default W2_Box_Number2 W2_Box_Label2 W2_Box_Number3 W2_Box_Label3 W2_Box_Number4 W2_Box_Label4 Fiscal_Yr_Max Employer_Max_Match Based_on_Record_Type Pay_Unit Pay_Unit_Period Pay_Period Pay_Per_Period Earnings_Code Court_Date Court_Name Document_Description Original_Amount Amount_Taken Vendor_ID Fed_Max_Deduction State_Max_Deduction Tip_Type Pay_Advance Accrue_Vacation Accrue_Sick_Time Workers_Comp_Code Pay_Advance_Taken Shift_Code Pay_Factor Pay_Step_Table_ID Base_Step_Increases_On Step Step_Effective_Date Step_FTE Work_Flow_Approval_Status Work_Flow_Priority

You can download it from the link below, or e-mail me for a copy.

http://community.gpug.com/Go.aspx?c=ViewDocument&DocumentKey=50d9b79e-5582-407c-81bd-92912bcfd211

The text of the code is below:

/*  By: Leslie Vail
    Date: 02/01/2014
    Description: This creates a view that combines the Pay Code, Benefit and Deduction code tables into a
      single view. This can be used to create a great SmartList or Excel report so that you can view
      the current amounts from a single list.

    Tables included:    UPR00400 (Pay Code)
                        UPR00500 (Deduction Code)
                        UPR00600 (Benefit Code)
                        UPR00100 (Employee Master)

*/

CREATE VIEW view_Payroll_Codes
as

/***********************************START WITH PAY CODE (UPR00400)**************************/

SELECT UPR00400.employid                                  AS Employee_ID
       ,lastname                                          AS ‘Last_Name’
       ,frstname                                          AS ‘First_Name’
       ,midlname                                          AS ‘Middle_Name’
       ,payrcord                                          AS Code
       ,’Paycode’                                         AS Code_Type
       ,CASE paytype
          WHEN 1 THEN ‘Hourly’
          WHEN 2 THEN ‘Salary’
          WHEN 3 THEN ‘Piecework’
          WHEN 4 THEN ‘Commission’
          WHEN 5 THEN ‘Business_Expense’
          WHEN 6 THEN ‘Overtime’
          WHEN 7 THEN ‘Double_Time’
          WHEN 8 THEN ‘Vacation’
          WHEN 9 THEN ‘Sick’
          WHEN 10 THEN ‘Holiday’
          WHEN 11 THEN ‘Pension’
          WHEN 12 THEN ‘Other’
          WHEN 13 THEN ‘Earned_Income_Credit’
          WHEN 14 THEN ‘Charged_Tips’
          WHEN 15 THEN ‘Reported_Tips’
          WHEN 16 THEN ‘Minimum_Wage_Base’
          ELSE ‘error’
        END                                               AS Pay_Type
       ,CASE UPR00400.inactive
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Inactive
       ,( CONVERT(DATETIME, ‘1900-01-01 00:00:00′, 102) ) AS Begin_Date
       ,( CONVERT(DATETIME, ‘1900-01-01 00:00:00′, 102) ) AS End_Date
       ,bspayrcd                                          AS Base_Pay_Record
       ,payrtamt                                          AS Amount
       ,CASE rptaswgs
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Report_As_Wages
       ,CASE sbjtfdtx
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_FedTax
       ,CASE sbjtssec
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_FICA
       ,CASE sbjtmcar
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_Medicare
       ,CASE sbjtsttx
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_SateTax
       ,CASE sbjtltax
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_LocalTax
       ,CASE sbjtfuta
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_FUTA
       ,CASE sbjtsuta
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_SUTA
       ,UPR00400.sutastat                                 AS SUTA_State
       ,ffedtxrt                                          AS Flat_FedTax_Rate
       ,flsttxrt                                          AS Flat_StateTax_Rate
       ,’-‘                                               AS Variable
       ,’-‘                                               AS Frequency
       ,CASE taxable
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Taxable
       ,0                                                 AS Based_on_Records
       ,bsdonrte                                          AS Based_on_Rate
       ,’-‘                                               AS Method
       ,’-‘                                               AS Formula
       ,0                                                 AS ‘Percent’
       ,0                                                 AS Tier_Max
       ,0                                                 AS Tier_Max_Units
       ,mxpypper                                          AS Max_per_Pay_Period
       ,0                                                 AS Max_per_Year
       ,0                                                 AS Lifetime_Max
       ,w2bxnmbr                                          AS W2_Box_Number
       ,w2bxlabl                                          AS W2_Box_Label
       ,UPR00400.noteindx                                 AS Note_Index
       ,0                                                 AS Note_Index2
       ,0                                                 AS Life_to_Date_Amt
       ,CASE dataentdflt
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Data_Entry_Default
       ,w2bxnmbr2                                         AS W2_Box_Number2
       ,w2bxlabl2                                         AS W2_Box_Label2
       ,w2bxnmbr3                                         AS W2_Box_Number3
       ,w2bxlabl3                                         AS W2_Box_Label3
       ,w2bxnmbr4                                         AS W2_Box_Number4
       ,w2bxlabl4                                         AS W2_Box_Label4
       ,0                                                 AS Fiscal_Yr_Max
       ,0                                                 AS Employer_Max_Match
       ,0                                                 AS Based_on_Record_Type
       ,payunit                                           AS Pay_Unit
       ,CASE payunper
          WHEN 1 THEN ‘WeeEkly’
          WHEN 2 THEN ‘Biweekly’
          WHEN 3 THEN ‘Semimonthly’
          WHEN 4 THEN ‘Monthly’
          WHEN 5 THEN ‘Quarterly’
          WHEN 6 THEN ‘Semiannually’
          WHEN 7 THEN ‘Annually’
          WHEN 8 THEN ‘Daily_Miscellaneous’
          ELSE ‘Error’
        END                                               AS Pay_Unit_Period
       ,CASE payperod
          WHEN 1 THEN ‘Weekly’
          WHEN 2 THEN ‘Biweekly’
          WHEN 3 THEN ‘Semimonthly’
          WHEN 4 THEN ‘Monthly’
          WHEN 5 THEN ‘Quarterly’
          WHEN 6 THEN ‘Semiannually’
          WHEN 7 THEN ‘Annually’
          WHEN 8 THEN ‘Daily_Miscellaneous’
          ELSE ‘Error’
        END                                               AS Pay_Period
       ,payprprd                                          AS Pay_Per_Period
       ,’-‘                                               AS Earnings_Code
       ,( CONVERT(DATETIME, ‘1900-01-01 00:00:00′, 102) ) AS Court_Date
       ,’-‘                                               AS Court_Name
       ,’-‘                                               AS
        Document_Description
       ,0                                                 AS Original_Amount
       ,0                                                 AS Amount_Taken
       ,’-‘                                               AS Vendor_ID
       ,0                                                 AS Fed_Max_Deduction
       ,0                                                 AS State_Max_Deduction
       ,CASE tiptype
          WHEN 1 THEN ‘Directly’
          WHEN 2 THEN ‘Indirectly’
          ELSE ‘Error’
        END                                               AS Tip_Type
       ,payadvnc                                          AS Pay_Advance
       ,CASE acruvacn
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Accrue_Vacation
       ,CASE acrustim
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Accrue_Sick_Time
       ,UPR00400.wrkrcomp                                 AS Workers_Comp_Code
       ,pyadvtkn                                          AS Pay_Advance_Taken
       ,shftcode                                          AS Shift_Code
       ,payfactr                                          AS Pay_Factor
       ,pystptblid                                        AS Pay_Step_Table_ID
       ,base_step_increased_on                            AS Base_Step_Increases_On
       ,step                                              AS Step
       ,step_effective_date                               AS Step_Effective_Date
       ,step_fte                                          AS Step_FTE
       ,CASE UPR00400.workflow_approval_status
          WHEN 1 THEN ‘Not_Submitted’
          WHEN 2 THEN ‘Submitted’
          WHEN 3 THEN ‘Not_Needed’
          WHEN 4 THEN ‘Pending_Approval’
          WHEN 5 THEN ‘Pending_Changes’
          WHEN 6 THEN ‘Approved’
          WHEN 7 THEN ‘Rejected’
          WHEN 8 THEN ‘Ended’
          WHEN 9 THEN ‘Not_Activated’
          WHEN 10 THEN ‘Deactivated’
          ELSE ‘ERROR’
        END                                               AS Work_Flow_Approval_Status
       ,CASE UPR00400.workflow_priority
          WHEN 1 THEN ‘Low’
          WHEN 2 THEN ‘Normal’
          WHEN 3 THEN ‘High’
          ELSE ‘ERROR’
        END                                               AS Work_Flow_Priority
FROM   UPR00400
       INNER JOIN UPR00100
               ON UPR00400.employid = UPR00100.employid
              
/*********************************** START OF DEDUCTION (UPR00500)**************************/

UNION
SELECT UPR00500.employid                                  AS Employee_ID
       ,lastname                                          AS ‘Last_Name’
       ,frstname                                          AS ‘First_Name’
       ,midlname                                          AS ‘Middle_Name’
       ,deducton                                          AS Code
       ,’Deduction’                                       AS Code_Type
       ,’-‘                                               AS Pay_Type
       ,CASE UPR00500.inactive
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Inactive
       ,dedbegdt                                          AS Begin_Date
       ,dedenddt                                          AS End_Date
       ,’-‘                                               AS Base_Pay_Record
       ,dedcamnt_1                                        AS Amount
       ,’-‘                                               AS Report_As_Wages
       ,CASE sfrfedtx
          WHEN 0 THEN ‘No’
          WHEN 1 THEN ‘Yes’
          ELSE ‘ERROR’
        END                                               AS Subj_to_FedTax
       ,CASE shfrfica
          WHEN 0 THEN ‘No’
          WHEN 1 THEN ‘Yes’
          ELSE ‘ERROR’
        END                                               AS Subj_to_FICA
       ,CASE shfrficamed
          WHEN 0 THEN ‘No’
          WHEN 1 THEN ‘Yes’
          ELSE ‘ERROR’
        END                                               AS Subj_to_Medicare
       ,CASE shfrsttx
          WHEN 0 THEN ‘No’
          WHEN 1 THEN ‘Yes’
          ELSE ‘ERROR’
        END                                               AS Subj_to_SateTax
       ,CASE sfrlcltx
          WHEN 0 THEN ‘No’
          WHEN 1 THEN ‘Yes’
          ELSE ‘ERROR’
        END                                               AS Subj_to_LocalTax
       ,’-‘                                               AS Subj_to_FUTA
       ,’-‘                                               AS Subj_to_SUTA
       ,’-‘                                               AS SUTA_State
       ,0                                                 AS Flat_FedTax_Rate
       ,0                                                 AS Flat_StateTax_Rate
       ,CASE vardedtn
          WHEN 0 THEN ‘No’
          WHEN 1 THEN ‘Yes’
          ELSE ‘ERROR’
        END                                               AS Variable
       ,CASE dednfreq
          WHEN 1 THEN ‘Weekly’
          WHEN 2 THEN ‘Biweekly’
          WHEN 3 THEN ‘Semimonthly’
          WHEN 4 THEN ‘Monthly’
          WHEN 5 THEN ‘Quarterly’
          WHEN 6 THEN ‘Semiannually’
          WHEN 7 THEN ‘Annually’
          WHEN 8 THEN ‘Daily_Miscellaneous’
          ELSE ‘Error’
        END                                               AS Frequency
       ,CASE txshanty
          WHEN 0 THEN ‘No’
          WHEN 1 THEN ‘Yes’
          ELSE ‘ERROR’
        END                                               AS Taxable
       ,bsdorcds                                          AS Based_on_Records
       ,0                                                 AS Based_on_Rate
       ,CASE dednmthd
          WHEN 1 THEN ‘Percent_of_Gross’
          WHEN 2 THEN ‘Percent_of_Net’
          WHEN 3 THEN ‘Fixed_Amount’
          WHEN 4 THEN ‘Amount_Per_Unit’
          ELSE ‘ERROR’
        END                                               AS Method
       ,CASE dedfrmla
          WHEN 0 THEN ‘No’
          WHEN 1 THEN ‘Yes’
          ELSE ‘ERROR’
        END                                               AS Formula
       ,dednprct_1                                        AS Percent
       ,dedtrmax_1                                        AS Tier_Max
       ,detrmxun_1                                        AS Tier_Max_Units
       ,depyprmx                                          AS Max_per_Pay_Period
       ,dedyrmax                                          AS Max_per_Year
       ,dedltmax                                          AS Lifetime_Max
       ,w2bxnmbr                                          AS W2_Box_Number
       ,w2bxlabl                                          AS W2_Box_Label
       ,UPR00500.noteindx                                 AS Note_Index
       ,UPR00500.noteindx2                                AS Note_Index2
       ,ltddedtn                                          AS Life_to_Date_Amt
       ,CASE dataentdflt
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Data_Entry_Default
       ,w2bxnmbr2                                         AS W2_Box_Number2
       ,w2bxlabl2                                         AS W2_Box_Label2
       ,w2bxnmbr3                                         AS W2_Box_Number3
       ,w2bxlabl3                                         AS W2_Box_Label3
       ,w2bxnmbr4                                         AS W2_Box_Number4
       ,w2bxlabl4                                         AS W2_Box_Label4
       ,deduction_fiscal_yr_max                           AS Fiscal_Yr_Max
       ,0                                                 AS Employer_Max_Match
       ,0                                                 AS Based_on_Record_Type
       ,’-‘                                               AS Pay_Unit
       ,’-‘                                               AS Pay_Unit_Period
       ,’-‘                                               AS Pay_Period
       ,0                                                 AS Pay_Per_Period
       ,earningscode                                      AS Earnings_Code
       ,courtdate                                         AS Court_Date
       ,courtname                                         AS Court_Name
       ,docdescr                                          AS Document_Description
       ,originalamount                                    AS Original_Amount
       ,amnttaken                                         AS Amount_Taken
       ,vendorid                                          AS Vendor_ID
       ,fedmaxded                                         AS Fed_Max_Deduction
       ,statemaxded                                       AS State_Max_Deduction
       ,’-‘                                               AS Tip_Type
       ,0                                                 AS Pay_Advance
       ,’-‘                                               AS Accrue_Vacation
       ,’-‘                                               AS Accrue_Sick_Time
       ,’-‘                                               AS Workers_Comp_Code
       ,0                                                 AS Pay_Advance_Taken
       ,’-‘                                               AS Shift_Code
       ,0                                                 AS Pay_Factor
       ,’-‘                                               AS Pay_Step_Table_ID
       ,0                                                 AS Base_Step_Increases_On
       ,0                                                 AS Step
       ,( CONVERT(DATETIME, ‘1900-01-01 00:00:00′, 102) ) AS Step_Effective_Date
       ,0                                                 AS Step_FTE
       ,’-‘                                               AS Work_Flow_Approval_Status
       ,’-‘                                               AS Work_Flow_Priority
FROM   UPR00500
       INNER JOIN UPR00100
               ON UPR00500.employid = UPR00100.employid
              
/*********************************** START OF BENEFIT (UPR00600)**************************/

UNION
SELECT UPR00600.employid                                  AS Employee_ID
       ,lastname                                          AS ‘Last_Name’
       ,frstname                                          AS ‘First_Name’
       ,midlname                                          AS ‘Middle_Name’
       ,benefit                                           AS Code
       ,’Benefit’                                         AS Code_Type
       ,’-‘                                               AS Pay_Type
       ,CASE UPR00600.inactive
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Inactive
       ,bnfbegdt                                          AS Begin_Date
       ,bnfenddt                                          AS End_Date
       ,’-‘                                               AS Base_Pay_Record
       ,bnfitamt_1                                        AS Amount
       ,’-‘                                               AS Report_As_Wages
       ,CASE sbjtfdtx
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_FedTax
       ,CASE sbjtssec
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_FICA
       ,CASE sbjtmcar
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_Medicare
       ,CASE sbjtsttx
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_SateTax
       ,CASE sbjtltax
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_LocalTax
       ,CASE sbjtfuta
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_FUTA
       ,CASE sbjtsuta
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Subj_to_SUTA
       ,’-‘                                               AS SUTA_State
       ,ffedtxrt                                          AS Flat_FedTax_Rate
       ,flsttxrt                                          AS Flat_StateTax_Rate
       ,CASE varbenft
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Variable
       ,CASE bnftfreq
          WHEN 1 THEN ‘Weekly’
          WHEN 2 THEN ‘Biweekly’
          WHEN 3 THEN ‘Semimonthly’
          WHEN 4 THEN ‘Monthly’
          WHEN 5 THEN ‘Quarterly’
          WHEN 6 THEN ‘Semiannually’
          WHEN 7 THEN ‘Annually’
          WHEN 8 THEN ‘Daily_Miscellaneous’
          ELSE ‘Error’
        END                                               AS Frequency
       ,CASE taxable
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Taxable
       ,bsdorcds                                          AS Based_on_Records
       ,0                                                 AS Based_on_Rate
       ,CASE bnftmthd
          WHEN 1 THEN ‘Percent_of_Gross’
          WHEN 2 THEN ‘Percent_of_Net’
          WHEN 3 THEN ‘Percent_of_Deduction’
          WHEN 4 THEN ‘Fixed_Amount’
          WHEN 5 THEN ‘Amount_Per_Unit’
          ELSE ‘ERROR’
        END                                               AS Method
       ,CASE bnffrmla
          WHEN 0 THEN ‘No’
          WHEN 1 THEN ‘Yes’
          ELSE ‘ERROR’
        END                                               AS Formula
       ,bnfprcnt_1                                        AS ‘Percent’
       ,bnftrmax_1                                        AS Tier_Max
       ,bntrmxun_1                                        AS Tier_Max_Units
       ,bnpaypmx                                          AS Max_per_Pay_Period
       ,bnfyrmax                                          AS Max_per_Year
       ,bnflfmax                                          AS Lifetime_Max
       ,w2bxnmbr                                          AS W2_Box_Number
       ,w2bxlabl                                          AS W2_Box_Label
       ,UPR00600.noteindx                                 AS Note_Index
       ,0                                                 AS Note_Index2
       ,ltdbnfit                                          AS Life_to_Date_Amt
       ,CASE dataentdflt
          WHEN 1 THEN ‘Yes’
          WHEN 0 THEN ‘No’
          ELSE ‘Error’
        END                                               AS Data_Entry_Default
       ,w2bxnmbr2                                         AS W2_Box_Number2
       ,w2bxlabl2                                         AS W2_Box_Label2
       ,w2bxnmbr3                                         AS W2_Box_Number3
       ,w2bxlabl3                                         AS W2_Box_Label3
       ,w2bxnmbr4                                         AS W2_Box_Number4
       ,w2bxlabl4                                         AS W2_Box_Label4
       ,benefit_fiscal_max                                AS Fiscal_Yr_Max
       ,emplrmaxmat                                       AS Employer_Max_Match
       ,borcdtyp                                          AS Based_on_Record_Type
       ,’-‘                                               AS Pay_Unit
       ,’-‘                                               AS Pay_Unit_Period
       ,’-‘                                               AS Pay_Period
       ,0                                                 AS Pay_Per_Period
       ,’-‘                                               AS Earnings_Code
       ,( CONVERT(DATETIME, ‘1900-01-01 00:00:00′, 102) ) AS Court_Date
       ,’-‘                                               AS Court_Name
       ,’-‘                                               AS Document_Description
       ,0                                                 AS Original_Amount
       ,0                                                 AS Amount_Taken
       ,’-‘                                               AS Vendor_ID
       ,0                                                 AS Fed_Max_Deduction
       ,0                                                 AS State_Max_Deduction
       ,’-‘                                               AS Tip_Type
       ,0                                                 AS Pay_Advance
       ,’-‘                                               AS Accrue_Vacation
       ,’-‘                                               AS Accrue_Sick_Time
       ,’-‘                                               AS Workers_Comp_Code
       ,0                                                 AS Pay_Advance_Taken
       ,’-‘                                               AS Shift_Code
       ,0                                                 AS Pay_Factor
       ,’-‘                                               AS Pay_Step_Table_ID
       ,0                                                 AS Base_Step_Increases_On
       ,0                                                 AS Step
       ,( CONVERT(DATETIME, ‘1900-01-01 00:00:00′, 102) ) AS Step_Effective_Date
       ,0                                                 AS Step_FTE
       ,’-‘                                               AS Work_Flow_Approval_Status
       ,’-‘                                               AS Work_Flow_Priority
FROM   UPR00600
       INNER JOIN UPR00100
               ON UPR00600.employid = UPR00100.employid
              
/***********************************END OF BENEFIT (UPR00600)**************************/
 
go

GRANT SELECT ON view_Payroll_Codes TO DYNGRP
 

Enjoy!

Leslie

Originally posted here:
SQL View Union of all Payroll Codes

June 4, 2014 · Leslie Vail · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 370

Modified GL Transaction Entry window available

I just uploaded a modified GL Transaction Entry window to the GPUG Share my code site. Here’s the link:

http://community.gpug.com/Go.aspx?c=ViewDocument&DocumentKey=93cb4f9c-3c1e-4082-bf7b-5548d0c162d0

Here’s what it looks like:

image

If you cannot access this on GPUG, and would like a copy of the .package file, please send me an e-mail. leslievail@earthlink.net

Enjoy!

Leslie

Continue reading here:
Modified GL Transaction Entry window available

June 3, 2014 · Leslie Vail · No Comments
Tags: , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 256

Customer (Sales) Posting Accounts

Here’s a document I did a bit ago to help explain what the posting accounts meant as an aid to setting up a new installation.

We’ll start with the SOP Setup window.

clip_image002

If you use Sales Order Processing or Invoicing, choose whether to use the posting

accounts for:

• Sales from the customer or the item

• Cost of Sales from the customer or the item

• Sales Returns from the customer or the item

Sales > Cards > Customer (Accounts Button)

clip_image004

About Customer Accounts

The accounts entered on a customer card are used to default on to a transaction. Cash is always defaulted from the Checkbook If the Checkbook radio button is highlighted as in the screenshot above. If you do not have an account specified on the Customer Account Maintenance, the system looks to the company posting account defaults from the Posting Accounts Setup window (below). You will notice that not every account on the Posting Accounts Setup window appears on the Customer Account Maintenance window.


Administration Series > Setup > Posting > Posting Accounts

clip_image006

Accounts Receivable

The default account that receivable amounts for this customer will be posted to.

DR Accounts Receivable

CR Sales

Sales

The default account where sales amounts for this customer will be posted. In SOP, this account is used only if ‘Posting Accounts From’ is set to Customer.

DR Accounts Receivable

CR Sales

Cost of Sales

The default account where cost of sales amounts for this customer will be posted. The dollar amount of ‘Cost of Sales’ is not determined until the Invoice is actually posted. This is because the cost is not known until the invoice is posted. The FIFO layer cost is not known. This is a Cost of Goods Sold account. In SOP, this account is used only if ‘Posting Accounts From’ is set to Customer.

DR Cost of Sales

CR Inventory

Inventory

I have never found a use for this account because Inventory is always taken from the item. If you know when it defaults in, please let me know :).

Terms Discount Taken

Terms discounts might be offered to your customers as an incentive to pay their account balances before the due date. In order to use this account as a default the ‘Track Discounts Available in General Ledger’ must be selected on the Receivables Setup window. This is typically an expense account.

DR Cash

DR Terms Discount Taken

CR Accounts Receivable

CR Terms Discount Available


Terms Discount Available

Terms discounts might be offered to your customers as an incentive to pay their account balances before the due date. In order to use this account as a default the ‘Track Discounts Available in General Ledger’ must be selected on the Receivables Setup window. This is an Asset account typically included in the Accounts Receivable category.

DR Accounts Receivable

DR Terms Discount Available

CR Sales

Finance Charges

If finance charges are assessed on this customer, this is the account that will be credited. This is typically an income account.

DR Accounts Receivable

CR Finance Charges

Writeoffs

This account is used when a customer doesn’t pay the full balance on a document and the remainder is written off. This is typically a bad debt expense account, or a debit to the reserve for bad debts contra asset account.

DR Writeoffs

CR Accounts Receivable

Overpayment Writeoffs

This account is used when a customer pays more than the full balance and the overpayment is taken into income.

DR Accounts Receivable

CR Overpayment Writeoff


Sales Order Returns

This account is used when return documents are used in sales transactions. This is typically a contra account to sales revenue. Often, it is taken against Sales instead of using a separate account.

DR Sales

CR Sales Returns

Read More:
Customer (Sales) Posting Accounts

May 26, 2014 · Leslie Vail · No Comments
Tags: , , , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 302

The Dynamics GP Blogster in Johannesburg, South Africa

My travels will take me to the beautiful and cosmopolitan city of Johannesburg, South Africa to conduct Microsoft Dexterity training for customers in the area. I will be there from Sunday May 25, 2014 through Saturday, May 31, 2014 and I am looking forward to meet a number of you.

I will be available in the afternoons after 6:00 PM local time and as time permits. For those of you who decide to tag along for dinner or otherwise, table topics are fairly open. If you are a prospective customer considering Microsoft Dynamics GP and would like some insight, please feel free to drop by as well. Please feel free to contact me by adding your comment to this blog and I will get in touch with you or click the email button on the right.
 
Until next post!

MG.-
Mariano Gomez, MVP
Intelligent Partnerships, LLC
http://www.intelligentpartnerships.com/

Taken from:
The Dynamics GP Blogster in Johannesburg, South Africa

May 25, 2014 · Mariano Gomez · No Comments
Tags: , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 316

New Dex.ini switches added to the list

Thanks to Rick Kohler I can add some new Dex.ini switches to my collection. The switches below control the behavior of the HITB (Historical Inventory Trial Balance) tool.

HITB_SkipReconciles=TRUE

Enables Step 2 without having to complete all of the reconciles. Thanks to Rick Kohler for this switch.

HITB_SkipErrorChecking=TRUE

Enables Step 3 regardless of whether errors exist during the data integrity checks. Thanks to Rick Kohler for this switch.

HITB_SkipClearingTransactions
=TRUE

Allows user to perform Step 5 regardless of whether balances still exist in the inventory accounts. Thanks to Rick Kohler for this switch.

HITB_SkipVersionChecks=TRUE

Allows the user to enter the window if on a version previous to build 1061. Thanks to Rick Kohler for this switch.

HITB_DebugFile=c:somefile.txt

Used to create a detailed debug log of the reset dictionary (Dynamics GP must be re-launched to take effect):

Taken from:
New Dex.ini switches added to the list

May 20, 2014 · Leslie Vail · No Comments
Tags: , , , , , , , , ,  · Posted in: Blogs I Follow Total Views: 368