What to Do with Exceptions

1. What to Do with Exceptions

2. Welcome Connect Conference 2013 This is an exciting opportunity for everyone involved. The benefits go beyond just training and education—collaborate with your peers and inspire others around you. educate • collaborate • inspire

3. What to Do with Exceptions This session will cover O/E exceptions, verification exceptions, OPUS exceptions, and wholesaler communication exceptions. Learn how to correct the SQL data validation exceptions, so you can utilize powerful tools within DDMS using SQL data. Uncover operational issues and minimize exceptions to help your company run more efficiently.

4. What to Do with Exceptions Agenda • Why is it important to review/fix exceptions? • Order Entry Exceptions • Verification Exceptions • Wholesaler Communication Exceptions • Exception (AKA Bad Margins) Report • O/PUS Exceptions • SQL validation and importing errors

5. What to Do with Exceptions Importance of reviewing and fixing • Time and money – who has those to throw away? • Utilizing powerful tools for efficiency and managing margins • Shopper’s experience online

6. What to Do with Exceptions Order Entry Exceptions • The Order Entry Exceptions report can be executed during day-end or printed from: • Reports Menu > Standard O/E (in Graphical) • The (TR) Order Entry Reports screen (in Text) • A prompt asks you about deleting file when printing — recommended that you say yes • Keep the report for at least 30 days

7. What to Do with Exceptions Order Entry Exceptions • The report shows the following exceptions: • Credit Limit or Past Due Overrides • Changed Pay Code • Salesperson Overrides • Price Overrides • Voided/Wasted Invoices

8. What to Do with Exceptions Order Entry Exceptions ECI2 ORDER ENTRY EXCEPTIONS 05/21/12 4400 ALLIANCE GATEWAY FWY 12:53:39 FORT WORTH TX 76177 LOCATION (ALL) PAGE 1 INVOICE # O.T. CUSTOMER KEY ITEM KEY DATE TIME EXCEPTION MESSAGE ========== ==== ============== ======================= ======== ======== ========================= 10003-0 102 10000 05/21/12 08:53:24 CREDIT LIMIT OVERRIDE - AUTH. BY DDMS 10014-0 102 10000 05/21/12 09:53:38 CREDIT LIMIT OVERRIDE - AUTH. BY DDMS 10242-0 102 10008 05/21/12 10:50:51 PAST DUE OVERIDE 10255-0 102 10004 4A1YG LEX 05/21/12 11:03:53 WASTED INVOICE # 108-0 101 10012WATR 2001036 GBC 05/21/12 11:12:26 PRICE OVERRIDE $ 14.210-> 14.090 10238-0 102 10002 446011BLK BND 05/21/12 12:08:21 PRICE OVERRIDE $ 116.250-> 100.000 10238-0 102 10002 7994BL SAF 05/21/12 13:08:34 PRICE OVERRIDE $ 146.250-> 100.000 10280-0 101 10012HUMN 75560 SMD 05/21/12 13:09:06 VOIDED TICKET 10282-0 101 10005 05/21/12 14:52:37 SALESMAN OVERRIDE ->

9. What to Do with Exceptions Verification Exceptions • The Verification Exception report can be executed during day-end, or printed from: • Reports Menu > Custom O/E (in Graphical) • The (T) Order Entry Selectors screen (in Text) • A prompt asks you about deleting file when printing • It is recommended that you say yes • Keep this report for at least 30 days

10. What to Do with Exceptions Verification Exceptions • The report shows the following exceptions: • Shipped Quantity Changed • Decreased On Hand • Added New Item Onto Order • Line Item Deleted

11. What to Do with Exceptions Verification Exceptions ECI2 VERIFICATION EXCEPTIONS 12:55:39 4400 ALLIANCE GATEWAY FWY LOCATION [ 1] 05/21/12 FORT WORTH TX 76177 DATE OF [05/21/12] PAGE 2 INVOICE # CUSTOMER KEY ITEM KEY TIME O.T. OLD PRICE O.QTY CD EXCEPTION DESCRIPTION ========== ============== ======================= ======== ==== ========= ===== === ================ 10274-0 10012HUMN 654YW MMM 11:41:49 101 100 Shipped Quantity Changed 10274-0 10012HUMN 32001 PIL 11:41:50 101 100 Shipped Quantity Changed 10274-0 10012HUMN 4560 IVR 11:41:49 101 1 109 Decreased On Hand 10274-0 10012HUMN 32001 PIL 11:41:50 101 2 109 Decreased On Hand 10274-0 10012HUMN 32001 PIL 11:23:47 101 262 Added New Item Onto Order 10275-0 10012HUMN 654YW MMM 11:26:15 101 300 Line Item Deleted 10275-0 10012HUMN 00010 CLI 11:26:15 101 300 Line Item Deleted 10278-0 10012 22001 UNV 11:22:56 101 2 206 Restock Amount Overridden 10278-0 10012 34004 AVT 11:22:55 101 262 Added New Item Onto Order 10278-0 10012 122 MMM 11:22:56 101 262 Added New Item Onto Order 10278-0 10012 22001 UNV 11:22:56 101 264 Order Quantity Changed 10279-0 10012 654YW MMM 11:24:33 101 300 Line Item Deleted

12. What to Do with Exceptions Wholesaler Communications The Cost Difference report: • Can be added to your day-end procedures • Shows that you transmitted a P/O with a certain cost and that the item(s) were acknowledged back at a different cost • Use the X Execute Report Selectors to print through the Specials (+) screen • Selectoris COSEXC

13. What to Do with Exceptions Wholesaler Communications

14. What to Do with Exceptions Wholesaler Communications ECI SUITE 154 PURCHASE ORDER COST EXCEPTIONS 05/21/12 4400 ALLIANCE GATEWAY FWY FORT WORTH TX 76177 COST LO ITEM NUMBER CO DATE TIME VENDOR TICKET # PO NUMBER COST SENT RECEIVED == ============== ======== ======== ======== ======== ========== ========== ========= ========= EXCEPTION: P/O Cost Discrepancy 1 36612 BSN 05/21/12 09:12021 SPARCO 167585 16929 2.310 2.400 1 CB336WN HEW 05/21/12 09:12029 SPARCO 167589 16929 31.270 29.890 1 69121 LLR 05/21/12 09:12034 SPARCO 16927 88.880 84.840 1 112103 HEW 05/21/12 09:12035 SPARCO 167587 16929 22.920 23.130 1 32001 PIL 05/21/12 10:52000 UNITED 10790 50171 7.490 7.580 1 654YW MMM 05/21/12 10:52001 UNITED 10790 50171 9.030 12.120 1 Q5421A HEW 05/21/12 10:52002 UNITED 10790 50171 289.690 347.110 1 654YW MMM 05/21/12 10:52007 UNITED 10792 50171 9.030 12.120 1 05160 AVE 05/21/12 14:02000 SPARCO 10793 50173 22.060 24.050

15. What to Do with Exceptions Wholesaler Communication The Quantity Exceptions report: • Can be added to your day-end procedures • Shows the difference in the quantity sent on a P/O and the quantity acknowledged • Use the X Execute Report Selectors to print through the Specials (+) screen • Selector is QTYEXC

16. What to Do with Exceptions Wholesaler Communication ERIC QA TEST SERVER PURCHASING QTY EXCEPTIONS 05/22/12 101 MAIN STREET FORT WORTH TX 76106 QTY QTY LO ITEM NUMBER CO DATE TIME VENDOR TICKET # PO NUMBER SENT RCVD == ============== ======== ======== ======== ======== ========== ========== ====== ====== 1 01615 SPR 02/01/11 13:48000 SPARCO 4241 SPA020110 12 50 1 01818 SPR 02/01/11 13:48001 SPARCO 4241 SPA020110 1 100 1 01828 SPR 02/08/11 10:12000 SPARCO 4251 SPA020810 1 25 1 500Q5945A CMP 03/01/11 15:26000 EDI 4273 DMI030110 1 2 1 CB436A HEW 03/01/11 15:26001 EDI 4273 DMI030110 1 3 1 CB436A HEW 03/01/11 15:26002 EDI 4273 DMI030110 1 2 1 500Q5945A CMP 03/02/11 14:51000 EDI 4276 DMI030210 1 2 1 CB436A HEW 03/02/11 14:51001 EDI 4276 DMI030210 1 3 1 CB436A HEW 03/02/11 14:51002 EDI 4276 DMI030210 1 2 1 CB436A HEW 03/02/11 14:51003 EDI 4277 DMI030210 1 3 1 CB436A HEW 03/02/11 14:51004 EDI 4277 DMI030210 1 2 1 CB436A HEW 03/02/11 14:51005 EDI 4278 DMI030210 1 2 1 500Q5945A CMP 03/02/11 14:51006 EDI 4280 DMI030210 1 2

17. What to Do with Exceptions Exception Report, AKA Bad Margins The Exceptions, or Bad Margins, report uses a margin % range to report any items outside that range. This report: • Can be added to your day-end functions • Lets you see items that have very low or very high margins • You can choose the percentages to use to see what falls outside that range

18. What to Do with Exceptions Exception Report, AKA Bad Margins • You can run the Exceptions report from: • Reports Menu > Standard Sales > Option B (in Graphical) • (TR) screen > Option B (in Text) • If you enter percentages 20 to 80, the report shows you any margin smaller than 20% or greater than 80%

19. What to Do with Exceptions Exception Report AKA Bad Margins

20. What to Do with Exceptions Exception Report, AKA Bad Margins ECI EXCEPTION REPORT 05/21/12 4400 ALLIANCE GATEWAY FWY 13:51:21 FORT WORTH TX 76177 GROSS % (20-80) PAGE 1 INVOICE # DATE TAKER ITEM NUMBER CO. DESCRIPTION C QTY COST PRICE GROSS % ========== ======== ==== ============== ======== ============================== = ===== ========= ========= ======= ********** SALESMAN:1009 CUSTOMER: 430 LANDMARK NATIVE SEED 10714-0 07/13/11 101 32001 SAN MARKER,PERM,SHARPIE,2TIP,BLK B 5 7.20 8.75 17.7% 10714-0 07/13/11 101 21200 UNV PAPER,XEROX/DUP,LTR,20#,WHT W 1 43.33 50.67 14.5% 10714-1 12/08/11 101 10010 CUI CUTTER,RTRY,HH,CC-10 1 7.25 9.02 19.6% 10716-0 07/13/11 101 6410131 PAP PEN,BP,RTR,CMFRTMT GRP,MED,BLU W 4 42.02 50.91 17.5% ********** SALESMAN:1013 CUSTOMER: 1018 SACRED HEART MEDICAL 10595-0 09/16/08 123 8377 AVE CRDS,BUS,INKJT,GY,2"X3.5",250 2 20.00 20.09 00.4% 10595-1 12/17/08 123 34551 CLI BAR,BINDING,1/8"X11"-BK 4 1 9.80 11.37 13.8% ********** SALESMAN:1014 CUSTOMER: 1600002 LUKINS & ANNIS, PS 10781-0 12/13/11 101 75001 ELI RIBBON CRG,FAX,THERM,PC201 W 1 16.47 20.09 18.0% 10781-1 03/14/12 101 45000 CGO RTL 2M USB A/A EXT CBL 1 3.22 3.93 18.1%

21. What to Do with Exceptions Reports from SIMPLE – OPUS loads • There are several reports that print when you are running the SIMPLE program during OPUS updates • Reports you need to view and work through: • BAD-REFS • I-DUPS • Make-Buys • Exceptions

22. What to Do with Exceptions Reports from SIMPLE – OPUS loads – BAD-REFS Items are listed on the BAD-REFS file if: • The alternate information in the Item window should contain a reference item but does not • The item is referenced to a stock number that does not exist in the dealer’s database • The item causes loop: For example, Item A is referenced to Item B, Item B is referenced to Item C, and Item C is referenced to Item A

23. What to Do with Exceptions Reports from SIMPLE – OPUS loads – BAD-REFS This report will print however you can also: • View or print the BAD-REFS file from the (Z) Master Utilities screen • Go through your network to the WORK unit where the file is stored, copy the file, and open the file in Excel • Bad referenced items will cause problems in order entry and online shopping

24. What to Do with Exceptions Reports from SIMPLE – OPUS loads – BAD-REFS

25. What to Do with Exceptions Reports from SIMPLE – OPUS loads – I-DUPS • As files are updated, they are searched for items that match the database file. If an item number in your file matches or closely resembles one being updated, the item is written to the I-DUPS file. • Looks at item number/prefix for match or similar • Looks at UPC codes

26. What to Do with Exceptions Reports from SIMPLE – OPUS loads – I-DUPS • You must look at this report to determine if the item is truly a duplicate — do not just assume it is since it hit this report. • The key(s) you do not use can be set as reference-only items or deleted. • Before you delete an item, you must confirm that you need to • If you are a stocking dealer, you may need to correct I-AUX information

27. What to Do with Exceptions Reports from SIMPLE – OPUS loads – I-DUPS This report prints, butyou can also: • View or print the I-DUPS file from the (Z) Master Utilities screen • Go through your network to the WORK unit where the file is stored, copy the file, and open the file in Excel

28. What to Do with Exceptions Reports from SIMPLE – OPUS loads – I-DUPS

29. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Make-Buys Report • A Make-Buys exception file is created WORK unit based on the date and time of execution. The file begins with MB and is followed with the date and time that the make-buys was executed • For example, file MB03281342 would be from March 28th at 1:42 pm. • This report is critical to stocking dealers

30. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Make-Buys report • This exception report lists any items and their data fields that could not be divided equally • The UOM adjustments were made successfully on these items, but this file logs any remainder that you may wish to write off or otherwise account for • The most important ones to address are the ones that have the ON-HAND field listed as having items left over after the conversion

31. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions

32. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • The Opdb Load and Update report, aka Item Exceptions report, prints during the OPUS update procedures, or you can have it save to a file based on the SIMPLE parameter. • If you save to file, it saves to your download folder. Check the +IFI screen for download folder. • If you save as a file, the file will start with the letters OP, followed by month, day, and time stamp. For example, OP03281415 represents a file created on March 28, at 2:15pm.

33. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • This report includes the following information: • Item number, item description, and manufacturer • Selling and buying units of measure • Number of selling units in the buy unit • Old and new costs and old and new list prices • Percentage by which the item's price has increased or decreased and whether the item is included on a flyer

34. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • Not Found: No match to this item was found in your file. This does not necessarily mean that the item is not in your file. It simply means that a recognizable stock number was not found. • New Item: This is a Not Found item. The program continues based on the loading parameters. • ????-DEL: The item does not exist in your file, or the vendor has marked this item as deleted. It is not added.

35. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • Deleted: The item was marked as deleted by the vendor. If you specified to update the ID box in the Item window and the item exists in your database, this message indicates that the change is made. • No Match: The item on the database file does not have a vendor to match your wholesaler vendor.

36. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • No Whlsl: The item is carried by vendors that you did not select. • No Page #: The item does not have a catalog page number and you specified to add only cataloged items. • Ref Item: The item is marked as one that references another item number. No update occurs.

37. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • Not Added: The item is new in your system, but is not added because it is marked as deleted, has no page number, and so forth. • Not Stocked: You specified to add only items in the catalog, and this item is not in the catalog. It is not added.

38. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • Key Change:If you specified to change your primary vendor's item numbers and the database item is recognized under a number that is different than your primary vendor's, the item number is changed. • Updated:If the item was in your database in accordance with the load parameters, this item is updated. If the item is new but is marked as deleted by the vendor, it is not.

39. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • No Update: The item was found in your database and in accordance with your load parameters, but was not updated. • Written:This message confirms that the item was either updated or added to your database and that the item's data was written to your file. • Complete:This is the last item in the O/PUS file. This message may be overwritten by other messages.

40. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • Vend Swap:The item's vendor is being changed. It also displays the name of the vendor to which it is being changed. • Vendor Name:The name of the selected vendor. • Not Branch:Not carried in this branch. • # Not Chg:The item was listed in a stock class for which you indicated you did not need the item number changed.

41. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • Price Var: Price Variance item did not update. The units on the database file are very different from your selling unit of measure. On existing database items, if your unit does not exactly match any unit on the database file, your pricing is compared to the database file. If the list price for your selling unit of measure is more than 30% lower or 70% higher than the prices available for this item on the update, O/PUS cannot update the item.

42. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • You will need to fix your items that have the Price Variance exception. • You have to update the List Price based on your master unit of measure currently in your file. • It may require that you re-load OPUS after fixing so that item can be updated. • An item will never update until that Price Variance is fixed.

43. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions

44. What to Do with Exceptions Reports from SIMPLE – OPUS loads – Item Exceptions • Find the KnowledgeBase through the Customer Portal. • Review the documentation or video online: • http://www.ddms.com/Resources/Support/faq/ opus/OPUSload.pdf • http://www.ddms.com/Resources/Support/faq/ opus/opus_listprice.pdf • http://www.ddms.com/support/tutorial/ItemListPriceVariance.htm

45. What to Do with Exceptions EBS SQL Validation and Importing Exceptions • Use SQL data to utilize many useful tools in DDMS: • Customer Price Plans • Order and Purchase Order Analysis • Preview Reports (SQL based reports) • This is where DDMS functionality is moving • It is not as hard as you think

46. What to Do with Exceptions EBS SQL Validation and Importing Exceptions Validating your data — why is this important? • The Validate DBF data tool in the EBS SQL Import Control Panel checks each record to ensure compliance. • Keep in mind if you are using split volumes EBS only reads the L0 (zero) record for Location 1. • Errors are displayed to give you a chance to correct them before importing invalid or missing data.

47. What to Do with Exceptions EBS SQL Validation and Importing Exceptions

48. What to Do with Exceptions EBS SQL Validation and Importing Exceptions • There is a Validate DBF Data Error Log that you can view. • This tells you where you have problems with data: • The file the problem is in • The keys to the record • What column (field) the problem is in • And the value • You must fix the data and re-validate if you want to use this data. This is critical for using the powerful tools of SQL.

49. What to Do with Exceptions EBS SQL Validation and Importing Exceptions

50. What to Do with Exceptions EBS SQL Validation and Importing Exceptions • After validating the data, you need to Import the data to build SQL tables. • You can schedule your imports — they do not have to be done manually. • There are two different types of imports: • Full Imports • Delta Imports