Wednesday, June 4, 2014

What’s behind this software anyway? Webinar 06/05/2014

If you want to become a better problem solver by knowing the behind-the-scenes actions of Dynamics GP, attend this webinar brought to you by GPUG.

help

The webinar starts at 1:00 Eastern time and lasts for an hour. Sign up at the GPUG Events site: http://www.gpug.com/events/gpugonesource123lgs06052014

This session will show you what happens when you double-click that GP icon or post a batch. You’ll learn how transactions flow through the Work, Open and History tables, what those terms really mean and how to find those specific data tables.

You will be more prepared to resolve issues by being able to interpret error messages and hone-in on where the problem might exist. You will no longer be in a panic when a posting crashes or mystery data shows up in an inquiry window. Take command of your system and reduce reliability on your Partner to get your software back up and running.

After the webinar, you can download the slide deck as well as several other documents I’ve prepared for the event. Come fill your toolbox!

Hope to ‘see’ you there!

Leslie

Tuesday, June 3, 2014

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 ,
          'N/A'                                                 AS Fed_Max_Deduction ,
          'N/A'                                                 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 ,
           'N/A'                                             AS Fed_Max_Deduction ,
           'N/A'                                             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

go
GRANT SELECT ON view_Payroll_Codes TO DYNGRP

Enjoy!
Leslie

Dex.ini switches download link

Hi all,

I just uploaded my DEX.INI switches list to the GPUG ‘Share my Code’ site. You can download it using the link below.

http://community.gpug.com/Go.aspx?c=ViewDocument&DocumentKey=c9bf846e-9487-4090-8960-c2457b8d3574

I added two more switches to it before I uploaded it, so this is the latest and greatest.

Enjoy!

Leslie

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

You can also download it from Box using this link:
https://app.box.com/s/xrd0fmz7b6rj6yoacfb8

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

Monday, May 26, 2014

Vendor Posting Accounts

This article was prepared to be used as an aid in setting up Vendor posting

accounts.  Purchasing Series > Cards > Vendor (Accounts Button) clip_image002 

Under Purchases ellipses button (circled above) clip_image004 

About Vendor Accounts The accounts entered on a vendor card are used to default on to a transaction. Cash is always defaulted from the Checkbook for this installation. If you do not have an account specified on the Vendor 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 Vendor Account Maintenance

window.  Administration Series > Setup > Posting > Posting Accounts clip_image005 

The accounts listed individually on the Vendor Account Maintenance window should not be the same as the accounts on the Posting Accounts Setup. Leave the Vendor Account Maintenance screen blank for otherwise matching accounts.  For instance, if you have one Accounts Payable account, do not enter this account at the vendor level. Enter it at the company level in the Posting Accounts Setup window. The only accounts entered directly to the card are exceptions to any company defaults.  If no default is found at the vendor level or the company level, the transaction account is left blank and the user has to enter the account in order to post the transaction.  I recommend not using a default purchases account at the company level unless it is a suspense account.

 Additional Vendor Accounts These are accounts that can be added as purchase accounts (the debit part of a payable transaction). You can enter an unlimited number of accounts here. Any accounts you mark ‘Default on Trx’ for appear in the transaction distribution window during transaction entry.  No dollar amount will default into these accounts, nor is one required for posting. In other words, you do not have to use each of the accounts that default onto the transaction. If you put an account in the Purchases field on the Vendor Account Maintenance window or the Posting Accounts Setup window, then the full dollar amount of the purchase will default into this account.  When in the distribution window, selecting the lookup button next to the account number field will display a lookup including only the accounts that have been added to the Additional Vendor Accounts window. The full list of accounts is also available, but this shorter list is displayed first. This feature is very helpful if a vendor has several accounts that are used in a single transaction. Good examples of this include employee expense accounts, credit card bills, travel agent fees, hotel bills, et cetera. 

Accounts Payable The default account that Payable amounts for this vendor will be posted to.  DR Purchases CR Accounts Payable 

Terms Discounts Available Terms discounts might be offered to you as an incentive to pay your 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 Payables Setup window. This is a liability account typically included in the Accounts Payable category.  DR Purchases CR Terms Discount Available CR Accounts Payable

Terms Discount Taken Terms discounts might be offered to you as an incentive to pay your 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 Payables Setup window. This is typically an income account. DR Accounts Payable  DR Terms Discount Available CR Terms Discount Taken CR Cash

 Finance Charges If finance charges are assessed by your vendor, this is the account that will be debited. This is typically an expense account.  DR Finance Charges CR Accounts Payable 

Purchases This account holds the amount of the purchase made from this vendor. This is typically an expense account, but can also be an asset account (inventory, fixed assets, pre-paids, et cetera). Only use this account if it is used nearly 100% of the time for this particular vendor.  DR Purchases CR Accounts Payable

Trade Discount This account is used when the vendor gives you an amount off of the entire invoice rather than an amount tied to a specific item. This decreases the balance of the invoice. This is typically posted to a expense account or a ‘contra’ expense account. I do not see this used frequently.  DR Purchases CR Trade Discount CR Accounts Payable 

Miscellaneous This account is used for miscellaneous amounts on a vendor invoice like handling charges that you want to track separately from the purchase. This increases the balance due on the invoice and is typically an expense account  DR Miscellaneous CR Accounts Payable 

Freight This account is used to record the amount charged by the vendor for freight. This increases the balance due on the invoice and is typically an expense account.  DR Freight CR Accounts Payable 

Tax This account is used to record the amount of sales tax charged by the vendor. This increases the balance due on the invoice and is typically an expense account. The only time I see this field used is when a company needs to accumulate ‘use’ tax, otherwise the entire amount due the vendor (including tax) is included in the Purchases amount.  DR Tax CR Accounts Payable 

Writeoffs This account is used when you do not pay the full balance owed the vendor and never intend to. The balance due is written off to take it from your accounts payable accounts. This is typically a contra expense account, or a credit to the original account that was debited when the vendor invoice was recorded.  DR Accounts Payable CR Writeoffs

Accrued Purchases This account is used when you receive items from the vendor but have not yet received the vendor’s invoice for the items. It acts as a temporary holding account for what will eventually be the account payable to the vendor. This is typically used in Purchase Order Receipt and is most likely associated with inventory or large purchases that require separate delivery processing. This is typically a liability account included with the Accounts Payable category. The balance is cleared out when the vendor’s invoice is received.  DR Purchases CR Accrued Purchases 

Purchase Price Variance This account is used for the price variance between a shipment unit cost and an invoice unit cost when posting an invoice receipt for a non-inventoried item using the Purchasing Invoice Entry window.  DR Accrued Purchases DR Purchase Price Variance CR Accounts Payable 

For example: a non-inventory item was received at $40

DR Purchases $40

CR Accrued Purchases $40 

the vendor invoiced the item at $45

DR Accrued Purchases $40

DR Purchase Price Variance $5

CR Accounts Payable $45

Until next time!
Leslie