SOP: Trade Discount Calculation
Automatically calculates the Trade Discount field on Sales Transaction Entry.
The Tweak uses a SQL Stored procedure to calculate the Trade Discount. The stored procedure can be edited to calculate the Trade Discount based on custom business rules.
Setup
Navigation: Tools >> Setup >> Company >> Setup GP PowerPack
Mark the box next to TWK-SOP: Trade Discount Calculation, then click DONE.
The feature will be enabled for the current user. Other users must restart their Dynamics GP client to enable this feature.
The GP PowerPack installation process creates a SQL Stored Procedure in the company database called wspTradeDiscountCalc. The stored procedure does not contain any code, so be default it will return $0.00. Enabling this Tweak before coding the stored procedure will result in Trade Discount always being set to $0.00.
The business logic for calculating the Trade Discount must be added to the stored procedure. WilloWare consulting services can be purchased to assist with modifying the stored procedure.
When a new Sales Transaction is created, Dynamics GP saves a mostly empty header record into the Sales Transaction Header table (SOP10100), and with existing transactions it does not update changes into SOP10100 until the document is saved. GP PowerPack creates a mirror table of SOP10100 called W5026SOPHdr, which is updated with data from the Sales Transaction Entry window so that the header fields are available to the wsopTradeDiscountCalc stored procedure. The following events cause the W5026SOPHdr table to be updated:
- Sales Line changes
- Sales Customer Detail Entry changes
- Primary Ship To Address Code change
- Sales Item Detail Entry changes
If the Trade Discount logic requires using SOP Header fields, use the W5026SOPHdr table rather than SOP10100.
The Trade Discount calculation should return a dollar amount, not a percentage. Sales Transaction Entry has several requirements on the Trade Discount Amount field, which will be applied to the dollar amount returned by the stored procedure.
- It cannot exceed the Subtotal. If it does, no change will be made to the Trade Discount.
- It can only be negative when the document is an Invoice and the Invoice has “returns” (i.e. negative quantity lines). If Trade Discount is negative, and negatives should not be allowed, no change will be made to the Trade Discount.
- The Trade Discount Amount must be compatible with the Currency Decimals used by the transaction. The Trade Discount Amount will be rounded to be compatible with the Currency Decimals.
Using Trade Discount Calculation
The Trade Discount will be automatically calculated and updated in the Sales Transaction Entry window when any of the following occur:
- Sales Line changes
- Sales Customer Detail Entry changes
- Primary Ship To Address Code change
- Sales Item Detail Entry changes
The Trade Discount Calculation can also be run manually by navigating to Additional >> Trade Discount Recalc.
The Trade Discount field will still be editable and will allow manual changes. However, manual changes to the Trade Discount field will be overwritten if any of the events described above trigger recalculation of the Trade Discount.