Spring Cleaning For Your Database Lindsay Riggs and Brian Mills

1. Spring Cleaning For Your Database Lindsay Riggs and Brian Mills

2. ....Keeping it Clean.... GIGO (Garbage In, Garbage Out): If you have garbage data in your software, then the output will be garbage too. It’s necessary for businesses to keep their data clean. The cleansing can happen whenever, but year end is the most common. We are recommending not to wait until year end. There is too many things moving at the end of the year with physical inventory counts, financial closings, etc. Instead, try to do your data cleansing on a more regular basis: monthly, quarterly, weekly. It’s easier to drill into issues and users recall ‘the story behind’ things better when it’s still fresh in their minds. Waiting 8 months to ask someone about an old open purchase order most likely won’t give you the results you want, because humans forget things all the time. It happens. Here are some system best practices for spring cleaning for your database.....

3. Purchase Documents • Purchase Quotes • Delete after xx days • Pull up your list view and make sure your Document & Posting Date fields are shown • Click in the date column you want to use to sort them ascending or descending by date • To delete: open the order and click the X “delete” icon • Click yes on this warning message • Continue until list is clean

4. Purchase Documents • Purchase Invoices • Not to be confused with Purchase Orders, Purchase Invoices can be entered without flowing them through a PO first, and they can be easily overlooked • Similar to cleaning Purchase Quotes, open your list view, display date field for ease of sorting • To delete: open the invoice and click the X “delete” icon • Click yes on this warning message • Continue until list is clean

5. Purchase Documents • Purchase Orders • Purchase Orders must be done prior to cleaning up sales orders • There are several scenarios that could apply to cleaning up Purchase Orders • 1) Use the system’s auto-delete function under Data Deletion in Administration Dept. • Click “delete invoiced Purchase Orders. • This will go through Purchase Orders and delete the ones that have been invoiced in full with no open lines remaining to be received/invoiced • 2) Not Received, Not Invoiced Purchase Orders • If truly not received, click X ‘delete’ on header • OR delete the individual lines and remove the header • information and you can re-use/recycle the PO#

6. Purchase Documents • Purchase Orders...(cont.) • 3) Received, Not Invoiced Purchase Orders • View each order on a case by case basis • Invoice the PO if the order is still valid • If the material was posted as received, but never • Invoiced, ask WHY? • If the invoice was paid via a different method (i.e. cash or credit card), enter the appropriate payment method code as such so the payment and invoice are posted at the same time to the vendor • If the vendor just never sent the invoice, leave the Direct Unit Costs on the lines so your Item Costing doesn’t get skewed, remove the Amount Including tax to auto-populate a 100% line discount, or manually enter your discount on the line • OR leave the line values alone, entering two GL lines to negate the GL postings that will happen you click “post” (debit Inventory and credit AP- most common)

7. Purchase Documents • Purchase Orders....cont. • 4)Item Charge lines • Post item charge with original Direct Unit Cost on PO • Then add a negative item charge line (applying it to same receipt) • Post • 5) G/L lines • Post the G/L line with Direct Unit Cost on PO • Then add a negative G/L line • Post

8. Purchase Documents • Purchase Orders....cont. • 6)Monthly review by AP • Sort the Purchase Orders by salesperson, take a screen shot, or paste into excel. • Hand out to purchasers/salespersons to look into why these are still open • 7) Alerts • Setup alerts for Unconfirmed PO’s • This notifies purchaser/salesperson that PO is unconfirmed & needs to be looked into

9. Customers & Vendors • Use the ‘blocked’ drop-down feature on the Customer Card. You have 3 options: • Ship: this selection blocks shipments to the customer • Invoice: this selection blocks any invoices from being posted • All: this one is self-explanatory......users can do nothing with said customer

10. Customers & Vendors • 2) Similar to customers, use the ‘blocked’ drop-down feature on the Vendor Card. You have 2 options: • Payment: this selection blocks payments being posted to the vendor • All: this one is self-explanatory......users can do nothing with said vendor

11. Customers & Vendors • 3) Delete them • The year MUST be closed and there can be no open ledger entries on the account • There can be no open documents for the account • Simply go to the card and click X ‘delete’ • Click “YES” if you truly want to delete them • The system will populate an error if there are open entries, and the year isn’t closed • PROS: Keep your customer and vendor lists clean • CONS: Some reports can error out if you attempt to filter by a deleted account

12. Terminated Employees • Whether they left, got fired, or you just need to make some tweaks, here’s how to keep your Users cleaned up in contractERP. • User Card: • Disable State • Set Expiry Date • Remove Permissions

13. Terminated Employees • 2) User Setup • Delete users settings by deleting the user setup card • 3) Salesperson/Purchaser • It’s recommend that you do not delete Salesperson/Purchaser Codes. Deleting will cause issues with any page/report where Code appears

14. Miscellaneous Cleaning

15. Miscellaneous Cleaning To reset UI customizations performed for multiple users, for example as a part of configuring a profile, use the Delete Profile Configuration window. The Delete User Personalization window only shows information about UI customization that you have performed, under your user logon.

16. Sales Documents • Sales Quotes • Delete after xx days • Review the Sales Quote List • Simplify the List – Sort on Document Date!

17. Sales Documents • Sales Quotes • Open Sales Quote in question • Review • Decide: Delete or Keep • Repeat steps until list is clean

18. Sales Documents • Sales Invoices / Sales Credit Memos • Don’t forget this list! • Review the Sales Invoice List • Simplify the List – Sort on Document Date!

19. Sales Documents • Sales Invoices • Open Sales Invoice in question • Review • Decide: Delete or Process • Repeat steps until list is clean

20. Sales Documents • Sales Orders / Sales Return Orders • Purge the list prior to review • YES, there’s a tool for that! • Delete Invoiced Sales Orders… • Sales Orders with All lines Qty = Qty Shipped = Qty Invoiced

21. Sales Documents • Sales Orders / Sales Return Orders • Review the Sales Order List • Simplify the List – Sort on Document Date!

22. Sales Documents • Sales Orders / Sales Return Orders • Investigate • Lines Not Shipped / Not Invoiced • All stock items? • Ship and Invoice to Customer -or- Delete • Special Order material received? • Call Customer to come get it! • -or- Delete and Return Items to Vendor or Re-Associate

23. Sales Documents • Sales Orders / Sales Return Orders • Lines Shipped / Not Invoiced • Invoice if still Valid • If not Valid • Items: Post Invoice with $0.00 Unit Price • Item Charge Lines: Enter negative Item Charge line applied to same shipment to offset and Post • G/L Entry Lines: Enter negative G/L Entry line to offset; Post

24. Sales Documents • Sales Orders • Stay Ahead of the game • with contractERP • There’s an for that! • Items Past Shipment Due Date? • Automatic notification to users Home screen are past shipment Due Date • Allows you to react before your Customer does

25. Shop and Work Orders • Review the Shop or Work Order list for older documents • Simplify the List – Sort on Document Date!

26. Shop and Work Orders • Review the Lines • Ready to Finish • NO? • PARTIAL? • YES? • Resolve the Lines to Resolve the Shop (Work) Order

27. Shop and Work Orders • Are the lines related to other documents? • Look to Source for Answers • Material List Lines • Sales Order lines • Purchase Order Lines

28. Contracts • What’s the goal in Contract cleanup? • Move Contracts to Completed State • Run the Contract Analysis Report

29. Contracts • Contract Analysis Report • Confirm: Remaining to Bill = $0.00 • Confirm: Payments to Date = Contract Amount

30. Contracts • Check for open Documents • Go to Master Contract for Global Navigate • Includes Base and all Change Orders

31. Contracts • Review List • Sales Order  • Sales Shipment / Sales Invoice • Purchase Order  • Purchase Receipt / Purch. Invoice • Shop Order  • Posted Shop Order

32. Contracts • Generate Close Out Report

33. Contracts

34. Contracts • Move Contract to Completed State • Advance Master Contract Status to Complete • Changes Base and All Change Orders! • We’ve reached our goal!

35. Items

36. Items • No longer planning to re-order a stock item? • Phase it Out • After quantity depleted • Remove Submittal Cross References • or Attributes • Rename to DO NOT USE • Block Item

37. Items • Dump those Dead Items • Qty On Hand = 0? • No transactions • in current Fiscal Year? • No plans to Restock? • Delete the Items

38. Items • Locate Stock Items in a more reliable way to reduce maintenance time! • Submittal Cross Reference requires an exact match of Description from Submittal Software • Example: McKinney Hinge TA2714 4-1/2” x 4-1/2” US26D • (make sure the spaces are right and the inch marks, etc.)

39. Items • Locate Stock Items in a more reliable way to reduce maintenance time! • Submittal Cross Reference requires an exact match of Description from Submittal Software • Example: McKinney Hinge TA2714 4-1/2” x 4-1/2” US26D • (make sure the spaces are right and the inch marks, etc.) • contractERP now accepting Attributes • from Submittal Software for Hardware match! • How it is written matters not, it is only the attributes

40. Thank you for attending our session! Spring Cleaning For Your Database Lindsay Riggs lriggs@accessit.com Brian Mills brian@accessit.com