Automatic Price calculation in Sales Transactions

By | November 30, 2020

In Sage X3, user can enter a Gross Price manually for any product in any Sales – Purchase transactions. But one of our client wanted to calculate gross price automatically while creating Sales transactions on the basis of LOT Numbers. To achieve this requirement, we did a customization on Product master, Work Order and Sales transaction screens. Refer the below example for better understanding:

At the product master, there is a checkbox named as “Schedule”. If it is checked then that particular product will be considered as Scheduled Product and if it is not checked then that particular product will be considered as Non-Scheduled Product. As you can see in the below screenshot for Product-014583R1, schedule checkbox is checked and tax rate is 12% which will be required further for price calculation.

New Stuff: How to delete Customer BP Invoice in Sage X3

[Product Master Screen]
[Product Master Screen]

At the Work order screen, there is one custom field “Price” at detail line level, where user will enter price manually along with the LOT number. There are three more custom fields –“MRP Price, Trade Price and Stockiest Price”. Calculation will be done based on the entered Price and then these prices will get associated with the entered LOT and will get saved in some custom table.

[Work Order]
[Work Order]

Formula for price calculation will be different for schedule and Non-schedule products as per the GST rate saved for each product in product master screen. Refer WO No – 1012011MFG00000001 as shown in the above screenshot to check the price calculation.

For Scheduled Formulations the Price Formula:

Price = Rs. 100/- and GST applicable is 12% (which is saved in Product master for Product 014583R1)

Formula will be: GST: (12% on Rs.100 i.e. 100*12/112) = 10.71
MRP after GST: Rs.100-10.71= 89.285
Trade Price: (16% margin on MRP after GST i.e. 89.29) = 75.000
Stockiest Price: (8% margin on PTR i.e.75.00) = 69.000
Billing Rate for Scheduled Formulation is Rs.69.000

For Non-Scheduled Formulations the Price Formula:

Price = Rs. 100/- and GST applicable is 12% (which is saved in Product master)

Formula will be: GST: (12% on Rs.100 i.e. 100*12/112) = 10.71
MRP after GST: Rs.100-10.71= 89.285
PTR: (20% margin on MRP after GST i.e. 89.29) = 71.428
PTS: (10% margin on PTR i.e.71.43) = 64.286
Billing Rate for Non-Scheduled Formulation is Rs.64.286

These prices will be flown to Sales delivery screen, the moment LOT is allocated to delivery. System will allocate the LOT on the basis of FIFO method which is the standard Sage X3 feature. Once LOT is allocated by the system then user can manually de-allocate the LOT and may select any other LOT if required.

In Sales delivery, gross price will get auto populated by Stockiest price after computing above formulation and gross price will remain non-editable.

[Sales Delivery]
[Sales Delivery]

Refer above screenshot in which Sales Delivery No is 1012011SDH00000032 and its Prices which are calculated in WO for Product-014583R1 and LOT No-LOT014 are flown automatically for same product and same LOT and calculated Gross price is 69.000 which is the stockiest price.

Hence with the help of this customization, Gross Price will be calculated automatically in the sales transactions on the basis of GST rate which is already saved in the product master.