Information Systems:Updating MMBC

Overview

This is a non-profit organization that is fully financed by industry to manage residential packaging and printed paper recycling programs in BC, either directly or by working with local governments, First Nations, private companies, and other non-profit organizations. (https://recyclebc.ca/)

• Notes: The goal is to have the manufacturer of a specific item be responsible for any packaging material and register/pay to MMBC the costs of recycling this material. For any manufacturer that is not registered with MMBC, the obligation falls to the distributor or retailer. uniPHARM has decided that we will pay (if needed) based on the sale of the item(s) to the retailers.

MMBC has developed volume thresholds that have changed since the program was first introduced. These can be found on the website below.

• https://recyclebc.ca/wp-content/uploads/2018/10/Recycle-BC-Low-Volume-Flat-Fees_2019.jpg

Instructions

• Typically this work would be done at the beginning of a year - looking at the data from the past year.

Download File to Excel

To generate the data file from the previous years sales information; In InfoNet, go to UniView / Sales Statistics / Summarized Item Sales. Set ‘From Year/Month’ and ‘To Year/Month’ so that the entire year is included, and click on ‘Search’. Then click on ‘MMBC to Excel’.

This will create an Excel file of item information, sales, and MMBC rates that have been previously uploaded. Items with main suppliers that are registered with MMBC are not included.

1. The file is saved on superserver/buying/common/Multi Material BC (example: sls2018.xlsx)
2. Open the previous years spreadsheet. Copy columns BN1:DR2 (headers) and past into current years spreadsheet.
3. Copy formulas from BO3:DQ3 and paste into current spreadsheet.
4. Copy formulas down to bottom of data records and paste on current spreadsheet.
5. Copy from column BO (go to the very bottom) and now copy the remaining rows that contain all the totals into the new spreadsheet. Note: you will probably have to edit the formulas to account for new data records.
6. Sort all the records by vendor name, item description
7. Apply filter to the column headers
8. On the website indicated above, obtain a current list of the stewards that are registered in BC. Suggestion is to print this list off (in 2019 this was 27 pages).
9. Based on the vendor name column, check on the print out to determine if any new vendors have registered in the past year. Keep track of vendors in our system that will need to have their business partner information updated.
10. Fictitious items (Nancy) can be removed from spreadsheet.
11. Internal supply items can be removed from spreadsheet.
12. For the remaining item records - need to determine the various column weights (grams) as needed - this can be obtained by contacting the manufacturer for the information OR physically inspecting and weighing the packaging material OR by estimation based on a similar item from the same manufacturer. NOTE: This step will take HOURS/DAYS/WEEKS to complete.
13. The spreadsheet will update/calculate the units sold times the various weights columns and provide totals at the bottom and far right columns. These totals at the bottom will be what is used when logging into the recyclebc website and completing our annual activity report.
14. Once all items have been reviewed & checked it is time to upload this spreadsheet back into the item master. Steps below.

Upload File from Excel

1. Save the updated file (the spreadsheet that you have been working on above) as CSV. This must be in the same format as was previously downloaded – you cannot insert or remove columns (however, you can add columns to the end; they will be ignored in the upload).
2. In InfoNet, go to Maintenance & Lists / MMBC / Upload CSV File. After it has been uploaded, the first 50 records of the file will be displayed. If they look correct – like, for example, everything is in the correct column – click on ‘Update’.

The upload runs WebSmart program IN_MMBC01, which calls CL program MMBC01 to copy the file from the IFS to the data base file MMBCINTF.

The update function in IN_MMBC02 calls CL program MMBC02, which calls RPG program MMBC03.

MMBC in InfoNet Partner Maintenance

A 'user defined field can be added to indicate whether or not the vendor is registered with MMBC. The options are -

In Progress
No            uniPHARM brings the product into BC, and is responsible
BC Resident   vendor that creates the paper and packaging is responsible 
Yes           vendor is responsible

MMBC in InfoNet Item Maintenance

If the main supplier is registered, the MMBC source is changed to 'Vendor Registered with MMBC', and not changes are allowed.

When changing the MMBC manually, the options are -

Averaged
BC Resident
Estimated
Manually Weighed
N/A
Vendor Registered with MMBC
Vendor Supplied
Yes