Additional Discount with formatted search

[former_member186712]

Hi all,

In our days the sales guys are more and more aggressive :twisted: 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.

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

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

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

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:

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. :smile:

Best regards,

Augusto Silva