Price List Update

Proposal summary

Marshall would like to make some modification on the previously developed Price List

Requirement 

Marshall would like the following changes on the Price List.

1. Need to display products in the same order as the attached excel file. 

Maybe you can convert the list to custom record in order to add a column for Display Order? Regardless, this is the way our sales directors want their products to show up on the price lists.

2. Remove Master Pack Columns (M – Q) from Broadcast and Cable price lists. These columns can stay in the MXL price list.
3. Is it possible to add “Freeze Panes” after row 3. We would like the header and row 3 to stay at the top when the customer scrolls down.
4. Please use “Price Lists” <no-reply@marshallelectronics.net> to send the emails from.
5. Please update the email template with the following changes:

Text for section above Sincerly: To ensure that you always receive these emails, please be sure to whitelist no-reply@marshallelectronics.net. You can do this by creating a new contact for this email address and by marking this email as “Not Junk”. Please note that this mailbox is not monitored so replying to this email will not receive a response.

6. For Item in Column A, please use Display Name instead of Item Name/Number.
7. For Price Lists with quantity pricing, can you make the columns 7 pts each column.

8. For customer accounts with Geo-Location = Domestic, please add the RSM to the header of the price list. Here is a sample and where to locate the RSM for each division.

9. Just like we did with UCC, we would like to split the Cables price lists into 4 separate price lists. One price list for each pricing group.

10. For file names please use Bulk Wire, Cable Assembly, Connectors, Pack Cable at the start of the file name.
11. Please provide an estimate as soon as possible and confirm if this can be done before the next scheduled email.

Our Solution

Description of Task 

The mentioned requirement can be achieved by updating the Price List creation script regardless of the method (Button on Customer, Bulk Customer Price List, and Scheduled Email)

1. We will  convert “Price List Group Title List” to a Custom record with an additional field for Order because it is used strictly for the price list. Then we will make use of that order to arrange the Item Groups within Price List
   
   1. The corresponding Price List Group Title will be included in the Price list only if at least a single item satisfied the Price Group Title.
2. The M to Q column will be removed from the Broadcast and Cable price list.
3. For specific email  sender, need to create an employee record with the given email (Access is not required) and will use that as the email sender
4. Email content will be modified directly in the template created in the custom record for the Price List
   1. On the email content “Column J” will be replaced with “Column G”
   2. Text for section above Sincerely: To ensure that you always receive these emails, please be sure to whitelist no-reply@marshallelectronics.net. You can do this by creating a new contact for this email address and by marking this email as “Not Junk”. Please note that this mailbox is not monitored so replying to this email will not receive a response.
5. Will use Display name instead of Item Name/Number, by assuming that the Display name will be unique for the Price List qualified Items
6. Need to adjust the “Your Price column” width to 7 pts per each column
7. Additional Rep details can be added in the header section for customer accounts with Geo-Location = Domestic
   1. Field mapping of the Manager based on the Price list will be statically included in the script. 

Price List	Regional Sales Manager Field
Broadcast	BROADCAST AV
Cables	CABLE/CONNECTOR
MXL	MXL
Optical	OPTICAL
UCC & UCC-Unrestricted	MXL UCC

2. Email and Phone Number will be taken from the standard fields from the corresponding employee record 
3. Need to split the Cables price lists into 4 separate price lists as follow

Pricing Group	File Name Prefix
Cables and Connectors : Bulk Wire	Bulk Wire
Cables and Connectors : Cable Assembly	Cable Assembly
Cables and Connectors : Connectors	Connectors
Cables and Connectors : Pack Cable	Pack Cable

So there will not be any Price List specifically for “Cable”

Assumptions

1. Price List Group Title List is not used for any other purpose than in the Item record as part of Price List creation. 
2.  The Display name of the item will be unique for the Price List qualified Items.

Risks

• We have created a search for checking the Display name count for the Price List item and found that all are unique. However, if there is any chance for the multiple items with same display names in the future, then we will have to consider that in the current update. Currently this is not considered.
• The freezing option can be confirmed only at the development phase. We tried to include the freezing in the current Price List sample. However, when we try to freeze particular rows, it creates sections and it is applied to those sections instead of specific rows. So we checked by Freezing the Top Raw. And we found that its XML code is much different from the previous XML code. So it may cause any issues for the macro that we have already defined for the Image population. So it is not included in this scope. We prefer to do this as a next scope after completion of the other updates by considering the tracked hours for Billing. 

Testing Plan

• We need to do the updates directly in the existing script. So we will have to disable the Price List creation options for Marshall users at development time
• After development, Internal Verification and UAT, we can enable the the Price List option for all users

Time

45 Hours

Task	Time Required
Analysis	2 hrs
Script update to complete the mentioned updates	36 hrs
Testing	6 hrs
Deployment and Documentation	1 hrs
Total	45 Hours