Proposal for Suitelet Pricing Report

Requirement  

 

Blackstone would like create Suitelet built that will allow sales department to export item pricing based on our discount matrix.  

The formula to calculate the effective price based on the Discount matrix is  

 

Item Base Price * (100 % – Discount percentage). 

 

For example, the Discount Code DC020 gives a 30% discount to any item with the HAN-070 Boil Out Units, Curing Units and Pressure Pots Category. If the BASE price was 100.00 and the discount is 30 %, then the math to get to the effective price is 100 * (100% or 1.0 – 30 % or 0.30) = $70.00.  

 

If the discount code is selected the price should be based on that discount code, and any item pricing should be displayed. Also please add a column for item pricing true or false.  

 

For example if the customer selected as Ace hardware and any the discount code column is also added then the pricing returned should be based on their item pricing and discount code results. If while ace hardware is selected the discount code filter is changed from their default discount pricing based on the selected discount code should be returned along with any item pricing they may have.  

 

The Suitelet should allow for effective pricing by selecting the customer , but must first check to make sure that there is not item pricing assigned to that customer.  

 

The Suitelet should also allow for just the selection of the discount code (with no customer) which would display all item pricing based on the discount matrix logic. 

 

The Suitelet should also allow for filtering of those returned items by division segment, item category, or item name.  

Finally the Suitelet should return the item name, base price, effective discounted price, item description, item category, item division segment, as well as the length width height UPC code HTC Code and weight of the item.  

 

Add a checkbox filter to include or omit inactive items. All of this data should be exportable to a CSV or XSLX. 

Deliverables   

 

The suitelet Report include the following Filters 

1. Customer – It will include the list of all active customers in NetSuite. 
2. Discount Code – It will include the list of all active discount codes from the custom record Discount matrix. 
3. Item Category – This will be custom list values of Item Category List in NetSuite. 
4. Divison Segment – Will be list of custom segment values sourcing from the custom record Division Segment.  
5. Item Name – This will be text box field to enter the specific item name. 
6. Inactive – This check box will  include or omit inactive items. 
7. Reset – For resetting the filter values.   

We will be adding separate buttons for displaying the report and downloading results based on the applied filters. 

The suitelet pricing report should have the following format.  

 

 

 

Case 1 : Item Pricing Enabled: Customer and Discount Code Filters Selected 

 

When a customer filter is selected and the selected customer has item pricing is enabled in the customer record for particular items and the discount code fitter is also selected and it is applied to the same item then, 

• The Base Price column includes the base price from the item record. 
• The Effective Price Column considers the item pricing from the customer record for that specific item. 
• Therefore, the Item Pricing Enabled column is designated as ‘Yes.’ 
• The Item Pricing column displays the item pricing values from the customer record for that particular item. 
• As this customer has item pricing enabled, instead of using the base price, the item pricing is utilized for the effective price calculation. Hence the Discount Code Price is determined by the following formula: 

             Discount Code Price = Item Pricing * (100 % – Discount code Percentage )    

• If the division segment is also chosen as the additional filter combinations, then the effective price is from the customer record, and which items have the specific division segment value in the item record.  
• If we use an additional filter as the item name in the above scenario only the specified item is displayed.  
• All the additional details along with the effective base price are, item description, item category, item division segment, as well as the length width height UPC code HTC Code and weight of the item. 

 

 

Case 2: Item Pricing not Enabled: Customer and Discount Code Filters Selected 

 

When the customer filter and the discount code filter is selected  and item pricing is not enabled in the customer record for the any items then, 

• The Base Price column contains the base price retrieved from the item record. 
• Consequently, the Effective Price Column reflects the Discount Code price calculated. 
• The Item Pricing Enabled column is marked as ‘No.’ 
• The Item Pricing column displays no values since there are no item pricing details in the customer record for this item. 
• Discount Code Price is determined by the following formula: 

           Discount Code Price = Base Price * ( 100 % – Discount code Percentage ) 

• If the division segment is also chosen as the additional filter combinations, then the effective price is based on the discount code  and which items have the specific division segment value in the item record.  
• If we use an additional filter as the item name in the above scenario only the specified item is displayed.  
• All the additional details along with the effective base price are, item description, item category, item division segment, as well as the length width height UPC code HTC Code and weight of the item. 

 

 

Case 3: Customer Filter Exclusively Selected 

 

• If the customer and item category are chosen as the filter combinations, then the effective price is from the customer record, and which items have the specific item category value in the item record.  
• If we have chosen the filters as customer, item category, and division segment then the result will be based on the effective price from the customer record, and which items have the specified item category-specific and division segment value in the item record.  
• If we use an additional filter as the item name in the above scenario only the specified item is displayed.  
• All the additional details along with the effective base price are, item description, item category, item division segment, as well as the length width height UPC code HTC Code and weight of the item. 

 

Case 4:  Discount Code Filter Exclusively Selected 

 

Effective Price = Base Price * ( 100 % – Discount code Percentage )  

 

• If the filter is chosen as the Discount code (without selecting the customer) then all the items which has the specific discount code applied is displayed here.  
• If the customer filter is not chosen and item category are chosen as the filter combination, then the effective price is based on the discount code calculation with which items have the specific item category value in the item record.  
• If we have chosen the filter discount code, item category, and division segment, then the effective price is based on the discount code calculation with which items have the specific item category value and the division segment values in the item record.  
• If we use the additional filter as the item name in the above scenario only the specified item is displayed.  
• All the additional details along with the effective base price are, item description, item category, item division segment, as well as the length width height UPC code HTC Code and weight of the item.   

 

Export the Results to CSV or XSLX 

 

 All of this data should be exportable to a CSV or XSLX. 

 

Assumptions and Limitations  

• Discount code filter is list of all active discount codes from the custom record Discount matrix. 
• Divison Segment  will be all active  list of custom segment values sourcing from the custom record Division Segment.  
• All details, apart from the effective price, will be sourced from the item record if corresponding values for the fields exist. Otherwise, these fields will remain empty in suitelet report. 
• If the item record includes a value for the description field, it will be added to the item description column in the suitelet report. If the description column does not exist in the item record, the sales description field will be considered for the item description column in the suitelet report.  
• The group pricing and price level from the custom record is not considerd. 
• We have not considered quantity pricing or multi-currency concepts for the pricing report.