Hi all,

In our days the sales guys are more and more aggressive 😈 therefore I had a challenge to create an additional discount at the line level in the sales documents. For example, we have some items with 30% discount and for certain customers the sales reps wanted to give an additional 6% but they wanted the customer to have on their Invoices 30+6% and not 34.20% the real discount value.

I was searching and I didn’t find anything like this and that’s the reason of this post.

This was my approach:

– I created a table (Discounts) and added 2 fields (Discount and Mask)

– In the Marketing Documents – Title I added a field (Discount) that receives the value of the discount in the BP Master data

– In the Marketing Documents – Rows I added a field (Mask) that will have the value that we want to print in the documents.

– In the Business Partners Master Data I added a field (Discount) that will have the additional discount of the customer.

Discounts Fields.jpg

In the Business Partner Master Data the user can insert the additional discount if the customer has a discount.

Customer Screen.jpg

In the discounts table, we have a relation between the discount and the discount mask that we want to be printed in the documents.

Discounts values.jpg

In the Sales Order form I used 3 formatted search’s to achieve the correct result.Sales Order FMS.jpg

1. FMS to get the customer discount from the Business Partner Master Data.

The FMS runs after typing the customer code.

Select U_Discount From OCRD Where OCRD.CardCode = $[$-4.0.0]

2. FMS that calculates the correct discount. The FMS runs and if the customer doesn’t have a special price, gets the discount from the Discount Groups – Manufactures. If the discounts are by Item Group or by Properties the code has to be changed.

The FMS is triggered after the item code.

If IsNull((SELECT T0.[Discount] FROM OSPP T0 WHERE T0.[CardCode] = $[$4.0.0] and  T0.[ItemCode]= $[$38.1.0]), 99) = 99

If (SELECT T1.[Discount] FROM OEDG T0  INNER JOIN EDG1 T1 ON T0.AbsEntry = T1.AbsEntry inner join OITM T2 on T2.FirmCode = T1.ObjKey WHERE T0.[ObjCode] = $[$4.0.0]  and  T1.[ObjType] = ’43’ and t2.itemcode = $[$38.1.0]) > 0
Select  (SELECT T1.[Discount] FROM OEDG T0  INNER JOIN EDG1 T1 ON T0.AbsEntry = T1.AbsEntry inner join OITM T2 on T2.FirmCode = T1.ObjKey WHERE T0.[ObjCode] = $[$4.0.0]  and  T1.[ObjType] = ’43’ and t2.itemcode = $[$38.1.0])
+ IsNull(((100 – (SELECT T1.[Discount] FROM OEDG T0  INNER JOIN EDG1 T1 ON T0.AbsEntry = T1.AbsEntry inner join OITM T2 on T2.FirmCode = T1.ObjKey WHERE T0.[ObjCode] = $[$4.0.0] and  T1.[ObjType] = ’43’ and t2.itemcode = $[$38.1.0])) * $[ORDR.U_Discount.0])/100, 0) from OCRD T1  WHERE T1.[CardCode]  = $[$4.0.0]

If IsNull((SELECT T0.[Discount] FROM OSPP T0 INNER JOIN SPP1 T1 ON T1.ItemCode = T0.itemcode and T1.Cardcode=T0.Cardcode  WHERE T0.[CardCode] = $[$4.0.0] and  T0.[ItemCode]= $[$38.1.0] and $[$10.0.Date] > T1.FromDate and $[$10.0.Date] < T1.ToDate), 99) = 99
If IsNull((SELECT T0.[Discount] FROM OSPP T0 WHERE T0.[CardCode] = $[$4.0.0] and  T0.[ItemCode]= $[$38.1.0]), 0) <> 0
SELECT T0.[Discount] FROM OSPP T0 WHERE T0.[CardCode] = $[$4.0.0] and  T0.[ItemCode]= $[$38.1.0]

3. FMS that gets the discount mask.

The FMS is triggered after the discount%.

If ($[$38.15.Number]) > 0

SELECT T0.[U_Mask] FROM [dbo].[@DISCOUNTS]  T0 WHERE T0.U_Discount =($[$38.15.Number])

This is how it looks like after typing a customer and some items:

Sales Order data.jpg

Then I made a change on the layouts to print the Disc.Mask if it’s filled otherwise it prints the discount.

I hope it helps someone and I’m looking forward for your comments or questions. 🙂

Best regards,

Augusto Silva

To report this post you need to login first.

3 Comments

You must be Logged on to comment or reply to a post.

Leave a Reply