46 Days: Weekend in the Weeds #2: Tables to convert from Purchasing to Tier2 (Part Number and Variations)
For the weekends, we’ll be head off into the weeds for (brief) discussion of database considerations…
(This debrief took a large automotive client 3 months of all day every day meetings, so this will only be the very highest of highlights. If you are interested in more detailed discussions, please reach out)
"Weekend in weeds” more on database structure
Tables you may find useful for conversion from Part Number and its Units of Measure UOM, which is how materials are typically tracking in Enterprise Resource Planning ERP/Purchasing systems, into Pounds which is the required Unit of Measure UOM for Tier2 Threshold Determinations:
Conversion table: Point to or maintain conversion factors from UOM’s you receive that can be converted directly (Gallons to Pounds for example). Volumetric Yes/No field useful so you know whether the SDS Specific Gravity will be needed for the conversion
Synonym table: It is likely that you will see multiple variations on the UOM’s you are trying to convert “to”, especially if manual data entry is conducted or between different Purchasing systems. G, Gal, GL, Gl, Gals, Gallons, for example.
SDS/Part Number: Include Source System, which is the particular ERP/Purchasing system that uses this Part Number. You will might have multiple part numbers for the same SDS, for example if there are differnt Part Numbers for different size containers of the same material. It is also likely that you will have multiple SDS’s for the same Part Number, reflecting different sources (manufacturers/suppliers) for the same material, so that supply chain issues can be minimized. Status (Active, Inactive with date, Pending) also a useful value to track, as well as starting and ending dates. If multiples for the dates, you can decide whether to have related table or just use the most recent round.
SDS/Container Descripton: This table maintains types of containers (including compound containers such as a Case of twelve 16oz cans) in which this SDS has been/is received. It contains the Conversion Factor, the “To Units” (Pounds for Tier2); and the derived data of Amount in Pounds This Container. This lets you put this single field onto subsequent queries instead of needing to re-derive the conversion each time you need to use it. This table would be used where the conversion factors are generic (like the Case of twelve 16oz cans) and not Part Number/Source System specific (such as Each or Piece)
SDS/PartNumber/Source/Container Descripton: This table maintains types of containers that are Part Number/Source System specific (such as Each or Piece). It contains the Conversion Factor, the “To Units” (Pounds for Tier2); and the derived data of Amount in Pounds Again, this lets you put this single field onto subsequent queries instead of needing to re-derive the conversion each time you need to use it.
These are the main tables for conversion activities from Purchasing Systems to Tier2 units (Pounds)
Community outreach: Have you had other Conversion Factor “adventures”? What were they?
#SARA312 #SARATierII #SARATier2 #EPCRA #EGLE #March1EPAReportDeadline #ERPIntegration #Purchasing #PartNumbersToPounds
Michigan EGLE links from first and second webinars of 2022 SARA 312 virtual training courses:
I’m keeping these links for awhile because they are so useful. I’ll post the session 3 recording info when it becomes available.
You can sign in and listen even if you didn’t sign up for the webinar series.
RECORDING LINK FOR SESSION 2: Wed Jan 19, 2022.
Really good example on batteries. This is the “in the weeds” how to conduct your threshold determinations episode: https://attendee.gotowebinar.com/recording/4027906237723673347
I talked to Mike Young, one of the presenters, right after the first webinar. He said that it’s not unusual for them to take phone calls from people from other states (with the warning to confirm that your state doesn’t have differences in their requirements compared to Michigan).
RECORDING LINK FOR SESSION 1:
https://attendee.gotowebinar.com/recording/7957790593170499843
PRESENTATION: (I wasn’t able to attach a copy of the presentation, email the link below and they will send it to you)
SARA TITLE III – TIER II REPORTING WEBSITE: http://www.michigan.gov/sara
MICHIGAN FACILITIES’ GUIDE TO SARA TITLE III: https://www.michigan.gov/documents/deq/deq-oea-saraguidebook_509720_7.pdf
CONTACT INFORMATION:
Mike Young and Adam Pohl
517-284-7272
