Alert - Sales Order Price vs Price List

Alert Overview:

This Alert notifies the user when a price is put onto a Sales Order that is different than what is in the price list for the customer.

Purpose of the Alert:

The purpose of this alert is to monitor users that have the ability to override a price.  If users have that ability, then they can change prices as they wish, use the wrong,  price, etc.  The alert will help this issue by notifying the recipient of any sales orders with a price that is different than what is in the price list for the customer.


It is recommended to run this report each morning to see all the sales orders from the previous day.

Example Results / Scenario:

Here's a great example we hear a lot that this alert will help with:

"In our old system, we did orders on a spreadsheet, so our sales entry people had to go lookup the price and put it into the sheet.  We had many sheets and they were always being updated all the time, so our users got in the habit of always going to look for the price and updating it.  Now that we are in Orchestrated and we have centralized price lists, we want our users to not ever update the price on a sales order."

The Alert will help with the above scenario, because it will notify the recipient anytime a user changes the price on a sales order to something that is different than what is on the price list.

 SQL Query:

This Alert Displays Sales Orders that have a Document Price that is different
Than the Price list for the Business Partner
It will show documents that have been updated in the last 7 days.
-- Here you can set the number of days prior to today that
-- will display in the alert
-- Replace this value (original value is 7)
Declare @NumDays AS INT = 7
-- These are the columns that will be displayed
Select 'Sales Order' AS 'Doc Type'
        , T0.[DocNum] AS 'Document #'
        , T0.[DocDate] AS 'Doc Date'
        , T0.[CardCode] AS 'Customer Code'
        , T0.[CardName] AS 'Customer Name'
        , T1.[ItemCode] AS 'Item Code'
        , T1.[Price] AS 'Document Price'
        , T3.[Price] as 'Price List Price'
        , (T1.[Price] - T3.[Price]) AS 'Difference $'
        , T4.[U_Name] AS 'Created By'
        , T5.[U_Name] AS 'Updated By'
        , T0.[UpdateDate] AS 'Update Date'
-- Here are the tables the data comes from
from ORDR T0
Inner Join RDR1 T1
ON T1.[DocEntry] = T0.[DocEntry]
ON T2.[CardCode] = T0.[CardCode]
Inner JOIN ITM1 T3
ON T3.[ItemCode] = T1.[ItemCode]
AND T3.[PriceList] = T2.[ListNum]
Inner Join OUSR T4
ON T0.[UserSign] = T4.[USERID]
Inner Join OUSR T5
ON T0.[UserSign2] = T5.[USERID]
Where T1.[Price] <> T3.[Price]
    AND DATEDIFF ( DAY ,T0.[UpdateDate] ,  GETDATE()) <= @NumDays   
For Browse




Was this article helpful?
0 out of 0 found this helpful