Never Forget to Match that Purchase Order Again!

Vote for Product Suggestion ID: 669719
https://connect.microsoft.com/dynamicssuggestions/feedback/details/669719/gp-never-forget-to-match-that-purchase-order-again

image

There are 18 votes so far. This process really works, I’ve had two of my suggestions actually make it into the product!

Too often a vendor invoice comes into the company and it’s entered into A/P without going through the invoice matching process in POP. We need a method for the user to be alerted if a vendor’s invoice is entered to the Payables transaction entry window while there is an outstanding uninvoiced receipt.

The problem is that we end up with uninvoiced POs,  or worse, the vendor is paid twice by mistake.

While you’re at the Connect page, I suggest you browse through all of the good suggestions that have been logged. You may find others that are worthy of your vote!

Until next time,

Leslie

Read the original post:
Never Forget to Match that Purchase Order Again!

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

Speed up your Macros

 

stick_figure_colored_running_inside_arrows_150_clr_9682

Today I delivered a Webinar on Macros for GPUG. I loved doing it, but there are so many things to cover that an hour just doesn’t do it justice. I talked about adding the ShowAdvancedMacroMenu=TRUE switch to your Dex.ini file to reveal additional functionality that can make macros a lot more robust. No new software to buy, just an extra line (called a switch) in the Dex.ini file.

Even with this switch, a macro still can’t throw up dialog boxes with yes/no/never mind buttons on it, but you can make your macros run significantly faster using this tool. It speeds them up to run at least twice as fast. This is especially useful if you’re doing large imports or mass changes. Macros running under this mode took half the time of others. For longer macros, it was even more astonishing.

After you add the above switch to your Dex.ini file, the Advanced Macro menu becomes an option on your Macro menu. Once you update your Dex.ini file, your menu will look like this:

Advanced Macro Menu

All of the goodies are in the Status window. Pictured below is the Status window. I’ll go through this window and point out the things that I like to use.

image

Below I’ll try to define each of the items numbered above:

1. Shows you the name of the macro currently being recorded and what line it’s on.

2. Shows you the name of the macro currently being played, and what line it’s on.

3. This is the magic checkbox that turbo charges your macro. Checking this box turns off the complete redraw and hides the interface flash thereby making the macro run twice (at least) as fast.

4. The line in the macro you want to jump to. This is a great way to find out what was going on when your macro fails at line 7463 for example. You can put that value in this field and the results will display in the scrolling window at the bottom. You can also restart a macro to begin playing at the jump to line.

5. The number of lines to display in the scrolling window.

6. Indicate where you want the macro to pause while it’s playing. This is a good way to debug the macro or stop it from executing after a certain point is reached

7. The actual lines of the macro according to the lines displayed

If there’s interest out there, I can go through each of the menu items on the Macro menu and the Advanced Macro and explain how to use it and what it does.

Until next time,

Leslie

See the article here:
Speed up your Macros

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

Richard Whaley’s memorial fund

Hi all,

I’ve been asked about where you could send a contribution in celebration of Richard’s life, and I now have that information. His family has established a memorial fund where you can send contributions that will be forwarded to the American Heart Association (AHA) in your name.

Richard was taken from us by congestive heart failure. The AHA is working to find a cure so that we do not lose any more colleagues that suffer the same problem as did our friend Richard. Here is the information you need to make contributions:

Bank of America
555 S. Hunt Club Blvd
Apopka, FL 32703

Routing number: 063100277
Account number: 898065822095

Thank you,

Leslie

Read more here:
Richard Whaley’s memorial fund

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

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: 352

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: 394

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: 406

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: 278