Requirement
Customer Sweetwater has special pricing on Mogami PJM items. Marshall team has created
a calculator in an excel file that looks for the quantity that these items are packed in from the
factory and if the quantity is divisible by that packed quantity, then the unit price applied to
that line would be the second last pricing. If the quantity ordered is not divisible by the factory
bag quantity, then the unit price will be based on the quantity pricing tier corresponding to
the quantity ordered.
The formula used to decide pricing
=IF (AND(MOD(N5/J5,1)>0, N5<10), C5, IF ( AND(MOD(N5/J5,1)>0, N5>=10, N5<25), D5, IF ( AND(MOD(N5/J5,1)>0, N5>=25, N5<50), E5, IF ( AND(MOD(N5/J5,1)>0, N5>=50, N5<100),F5,IF(N5>=250, H5, G5)))))
Orders for Sweetwater are currently entered via UI. However, once this is done, the Marshall
will be receiving these orders via EDI (Web Services). So, the script/workflow must be able to
run in both scenarios. Currently, this pricing model is only applied to Sweetwater. However,
there is a possibility that this pricing model might be used for other customers.
Solution
Prerequisites
The Marshall team will import data related to the customization before the production
deployment.
Custom Fields
- Checkbox in Customer Record
A custom check box field labeled “Special price customer” will be created for
recognising the customer for which special pricing is applicable. For now, the field will
be checked for the customer Sweetwater. - Factory Bag Field in item Record
A custom field will be created in item Record which defines the no: of quantity in a
factory bag. The field type will be integer type. If this field has value, the item will be
considered as a special price item. The minimum value will be given as 1 at the time
of the creation of the field to avoid accepting 0 or negative values. - Factory Bag Price
A custom field will be created in the Item record to store the factory bag price. Field
type will be currency.
Description of the Solution
CLIENT SCRIPT
For setting the unit price in UI, a client script must deploy on the creation of Sales orders. So,
upon adding each line, the proposed script will be setting the corresponding unit price by
checking the quantity entered is completely divisible by the factory bag. If it is, the value will
be taken from the factory bag price field in the item record. And if it is not divisible by the
factory bag, the quantity pricing set by NetSuite will be retained.
Conditions to add factory bag price
• “Special price customer” field should be checked in the customer record.
• Factory bag should contain value.
• Quantity % factory = 0.
Price Level
When adding the factory bag price in the unit price field of the item, the price level as custom will be
set in the line item of the sales order.
USER EVENT SCRIPT
For the orders created other than UI (like web services), a user event script will be deployed
to add the special price. On create context and if the execution context is not UI, the below
conditions will be checked to add the factory bag price for every item on saving the record.
Users can edit the sales order and will be able to change the price added by the script.
Conditions to add factory bag price
• “Special price customer” field should be checked in the customer record.
• Factory bag should contain value.
• Quantity % factory = 0.
Price Level
When adding the factory bag price in the unit price field of the item, the price level as custom will be
set in the line item of the sales order.
Assumption
• Factory bag is no: of quantity in the bag for a given item.
• If the factory bag is defined in an item, that item will be considered as special price
item, otherwise not.
• If the factory price field is empty, the price will be based on the quantity pricing set by
NetSuite.
• While the script handles the Price of an item (Quantity is fully divisible by the factory
bag), then it will be added as a Custom Price.
• Users can edit the price level after adding the price via script.
Risks
• Any type of currency like customer currency or sales order currency is not considered
for the value in the factory bag price field. Whatever the amount in the factory bag
price will be set in the unit price field.
• The price level of the customer cannot be considered when taking the price from
factory bag price field.
• The UI performance can be affected due to this additional customization along with
the already existing ones. However actual performance impact can be confirmed only
after the development.