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
Tuesday, June 3, 2014
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:
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
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:
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) 
Under Purchases ellipses button (circled above) 
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 
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
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.
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)
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
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
Wednesday, May 21, 2014
Dex.ini file settings document download
It has come to my attention that the link to the Dex.ini settings file is broken. In addition to updating the document, I created a new link so that you can once again download it if interested. Please let me know if you have any trouble so that I can fix it – and e-mail you a copy.
Here’s the new link: Dex.ini settings document also known as https://app.box.com/s/xkgae2rz1vil1ap4o579
Until next time!
Leslie
Here’s the new link: Dex.ini settings document also known as https://app.box.com/s/xkgae2rz1vil1ap4o579
Until next time!
Leslie
Tuesday, May 20, 2014
Concatenating a range of cells in EXCEL
I often use Excel to create SQL UNION statements and update statements by harnessing Excel's concatenate function. Typically, I’m creating UNION statements. I list the column names from the tables I’m trying to union and then use Excel until I get them lined up and mapped correctly. My final step is to use the CONCATENATE function to append the necessary comma between the column names.
Recently, I created a view that combined the pay code, deduction code, and benefit code tables. I later used that view to create a SmartList object so that all of those different codes could be viewed in one place. You can download the SmartList on GPUG's 'share my code' site. Or e-mail me for it (leslievail@earthlink.net). I used Excel to build the UNIONs and the CONCANTENATE function to insert the commas. While using CONCANTENATE accomplished my objective, it was a bit time consuming than I had hoped. There has to be a better way, I thought. And there is.
The CREATE_DELIMITED function. Using a function to join a range of cells together separated by a certain character (or characters) is much easier than using the CONCATENATE function. I found an article on the Internet the other day that described such a function. It works magnificently. If you use Excel to create SQL statements as I have described, I recommend that you copy this function into your Personal Macro Workbook. By creating the function in your macro workbook, you’ll be able to use it in all of your workbooks. Katie Giles published this function on her blog on September 12, 2009. I modified it slightly to fit my needs, but the logic was created by Katie Giles.
The following text will show you how to create and call the function, how it works, and its limitations.
Concatenating the old fashioned way To combine four cells (A1, B1, C1 and D1) into one field, separated by commas, you could use the following two options: 1. Hard code the concatenation using the CONCATENANTE function and include the literal string between each cell: =CONCATENATE (A1,", ",B1,", ",C1,", ",D1) 2. Alternatively, you can use the ampersand to join the cells together =A1&", "&B1&", "&C1&", "&D1 Both of the options above will return the desired output, but if you need to join many cells together, the formula could become quite long and hard to manage.
Using a function to concatenate cells Having a function that will accept an optional parameter for the delimiter is much more efficient than creating the formula by hand. The VBA code below creates the function I now use to create my unions. Example VBA Function
Function CREATE_DELIMITED (ByVal cellRange As Range, Optional ByVal delimiter As String)
Dim c AS Range
Dim DataStatement AS String
Dim Count AS Integer
Count = 0
DataStatement = ""
For Each c in cellRange
Count = Count + 1
DataStatement = DataStatement & c.Value
If Count < cellRange. Count Then
DataStatement = DataStatement & delimiter
End If
Next
CREATE_DELIMITED = DataStatement
End Function
(thanks to Perpetual Acumatica Learner for correcting one of the lines of this code)
How the CREATE_DELIMITED function works To call the function, you would use the following syntax: =CREATE_DELIMITED (range [, string]) The function accepts two input parameters, a range and a string. · range is the selection of cells you want to join together · string is the character(s) you want to use for the delimiter. This is an optional parameter. The function uses a count variable to check whether the current cell is the last one in the range. You need to know when you are on the last sell so that you don't append the delimiter to the end of the statement.
Joining a range of cells with the CREATE_DELIMITED function Using the CREATE_DELIMITED function to create the concatenated result would look like this: =CREATE_DELIMITED (A1:D1,", ") The range is A1:D1 and the string delimiter is a comma and space.
Joining cells from multiple rows If the range you want to join includes more than one row, Excel works through each row in turn. For example, if your function call resembled the following: =CREATE_DELIMITED (A1:D2,", ") Excel would join cells A to D on row 1 followed by cells A to D on row 2. You could also achieve this by using two function calls and concatenating them together. The formula would look like this: =CREATE_DELIMITED (A1:D1,", ") & ", " & CREATE_DELIMITED (A2:D2,", ") If you were to use the built-in CONCATENATE function, the formula would look like this: =CONCATENATE(A1,", ",B1,", ",C1,", ",D1,", ",A2,", ",B2,", ",C2,", ",D2) The formula using the CREATE_DELIMITED function is by far the easiest one to use.
CREATE_DELIMITED Function Limitations The CREATE_DELIMITED function is only suitable for joining a range of adjacent cells. This function cannot be used to join cells in different locations on a spreadsheet or to join text strings entered directly into the formula (rather than being entered into the cells themselves).
Until next post!
Leslie
Recently, I created a view that combined the pay code, deduction code, and benefit code tables. I later used that view to create a SmartList object so that all of those different codes could be viewed in one place. You can download the SmartList on GPUG's 'share my code' site. Or e-mail me for it (leslievail@earthlink.net). I used Excel to build the UNIONs and the CONCANTENATE function to insert the commas. While using CONCANTENATE accomplished my objective, it was a bit time consuming than I had hoped. There has to be a better way, I thought. And there is.
The CREATE_DELIMITED function. Using a function to join a range of cells together separated by a certain character (or characters) is much easier than using the CONCATENATE function. I found an article on the Internet the other day that described such a function. It works magnificently. If you use Excel to create SQL statements as I have described, I recommend that you copy this function into your Personal Macro Workbook. By creating the function in your macro workbook, you’ll be able to use it in all of your workbooks. Katie Giles published this function on her blog on September 12, 2009. I modified it slightly to fit my needs, but the logic was created by Katie Giles.
The following text will show you how to create and call the function, how it works, and its limitations.
Concatenating the old fashioned way To combine four cells (A1, B1, C1 and D1) into one field, separated by commas, you could use the following two options: 1. Hard code the concatenation using the CONCATENANTE function and include the literal string between each cell: =CONCATENATE (A1,", ",B1,", ",C1,", ",D1) 2. Alternatively, you can use the ampersand to join the cells together =A1&", "&B1&", "&C1&", "&D1 Both of the options above will return the desired output, but if you need to join many cells together, the formula could become quite long and hard to manage.
Using a function to concatenate cells Having a function that will accept an optional parameter for the delimiter is much more efficient than creating the formula by hand. The VBA code below creates the function I now use to create my unions. Example VBA Function
Function CREATE_DELIMITED (ByVal cellRange As Range, Optional ByVal delimiter As String)
Dim c AS Range
Dim DataStatement AS String
Dim Count AS Integer
Count = 0
DataStatement = ""
For Each c in cellRange
Count = Count + 1
DataStatement = DataStatement & c.Value
If Count < cellRange. Count Then
DataStatement = DataStatement & delimiter
End If
Next
CREATE_DELIMITED = DataStatement
End Function
(thanks to Perpetual Acumatica Learner for correcting one of the lines of this code)
How the CREATE_DELIMITED function works To call the function, you would use the following syntax: =CREATE_DELIMITED (range [, string]) The function accepts two input parameters, a range and a string. · range is the selection of cells you want to join together · string is the character(s) you want to use for the delimiter. This is an optional parameter. The function uses a count variable to check whether the current cell is the last one in the range. You need to know when you are on the last sell so that you don't append the delimiter to the end of the statement.
Joining a range of cells with the CREATE_DELIMITED function Using the CREATE_DELIMITED function to create the concatenated result would look like this: =CREATE_DELIMITED (A1:D1,", ") The range is A1:D1 and the string delimiter is a comma and space.
Joining cells from multiple rows If the range you want to join includes more than one row, Excel works through each row in turn. For example, if your function call resembled the following: =CREATE_DELIMITED (A1:D2,", ") Excel would join cells A to D on row 1 followed by cells A to D on row 2. You could also achieve this by using two function calls and concatenating them together. The formula would look like this: =CREATE_DELIMITED (A1:D1,", ") & ", " & CREATE_DELIMITED (A2:D2,", ") If you were to use the built-in CONCATENATE function, the formula would look like this: =CONCATENATE(A1,", ",B1,", ",C1,", ",D1,", ",A2,", ",B2,", ",C2,", ",D2) The formula using the CREATE_DELIMITED function is by far the easiest one to use.
CREATE_DELIMITED Function Limitations The CREATE_DELIMITED function is only suitable for joining a range of adjacent cells. This function cannot be used to join cells in different locations on a spreadsheet or to join text strings entered directly into the formula (rather than being entered into the cells themselves).
Until next post!
Leslie
Paste to GP from Excel - the wait is over!
This window is a mock up – but it’s close to the real thing. Included in the R2 release that is due out this spring, is the Paste feature! You will at last be able to past a general ledger entry from an Excel spreadsheet. It’s simple to set up and simple to do. First, your journal entry distributions need to be entered as below on a spreadsheet.
Next, highlight the information you want to put into your journal entry, and then put your curser on the first line of your journal entry. Finally, push the [Post] button and your distributions will populate the journal entry.
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 | 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): |
Friday, March 21, 2014
Using Copy and Paste in a GP Macro
What if you want to copy and paste using a GP Macro? You can easily do it, but there’s a little trick to it. Let’s learn how!
The requirement was to create a bunch of manual payments with the User ID of who entered the payment being entered into the comment field. While there are many ways to accomplish this task, the client determined that using a mail-merge macro would be the best way to go.
Since the user running the macro is not static, the User ID needed to be copied from the GP Welcome window. The relevant part of the recorded macro is shown below. The copy and paste lines are highlighted.
CommandExec dictionary 'default' form 'Command_System' command UserID
NewActiveWin dictionary 'default' form Login window Login
CommandExec form BuiLtin command cmdEditCopy
MoveTo field 'Cancel Button'
ClickHit field 'Cancel Button'
NewActiveWin dictionary 'default' form sheLL window sheLL
ActivateWindow dictionary 'default' form 'PM_Manual_Payments' window 'PM_Manual_Payments'
CommandExec form BuiLtin command cmdEditPaste
TypeTo field 'Transaction Description' , 'sa'
Instead of actually performing the standard copy and paste, the macro hard-coded what it copied and retains that value forever. That’s not what they want, they need the recorded User ID to change dynamically according to whomever is logged in.
The solution is simple. When you record your macro, choose ‘Select All’ from the Edit menu and then select ‘Copy’. Do the same when you go to paste and the macro will perform the copy and paste function correctly. Here’s what the new macro code looks like after the change:
CommandExec dictionary 'default' form 'Command_System' command UserID
NewActiveWin dictionary 'default' form Login window Login
CommandExec form BuiLtin command cmdEditSelectAll
CommandExec form BuiLtin command cmdEditCopy
MoveTo field 'Cancel Button'
ClickHit field 'Cancel Button'
NewActiveWin dictionary 'default' form sheLL window sheLL
ActivateWindow dictionary 'default' form 'PM_Manual_Payments' window 'PM_Manual_Payments'
MoveTo field 'User Defined 1'
CommandExec form BuiLtin command cmdEditSelectAll
CommandExec form BuiLtin command cmdEditPaste
MoveTo field 'User Defined 2'
Hopefully this little tip will help you record more dynamic macros.
Until next post!
Leslie
Subscribe to:
Posts (Atom)