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.

About Us

Greytrix – a globally recognized and one of the oldest Sage Development Partner is a one-stop solution provider for Sage ERP and Sage CRM organizational needs. Being acknowledged and rewarded for multi-man years of experience, we bring complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third-party add-on development and implementation competence.

Greytrix caters to a wide range of Sage X3, a Sage Business Cloud Solution, offerings. Our unique GUMU™ integrations include Sage X3 for Sage CRMSalesforce.com, Dynamics 365 CRM and Magento eCommerce along with Implementation and Technical Support worldwide for Sage X3. Currently we are Sage X3 Implementation Partner in East Africa, Middle East, Australia, Asia, US, UK. We also offer best-in-class Sage X3 customization and development services, integrated applications such as POS | WMS | Payment Gateway | Shipping System | Business Intelligence | eCommerce and have developed add-ons such as Catch – Weight  and Letter of Credit and India Legislation for Sage X3 to Sage business partners, end users and Sage PSG worldwide.

Greytrix is a recognized Sage champion ISV Partner for GUMU™ Sage X3 – Sage CRM integration also listed on Sage Marketplace; GUMU™ integration for Sage X3 – Salesforce is a 5-star rated app listed on Salesforce AppExchange and GUMU™ integration for Dynamics 365 CRM – Sage ERP listed on Microsoft AppSource.

For more information on Sage X3 Integration and Services, please contact us at x3@greytrix.com, We will like to hear from you.