alert query for price list deviation

[bennie1402]

Hi Experts,

I need a query where I can have an alert per document whenever the price set on the sales invoice is less than the default pricelist.(multiple pricelist of wich a specific one is assigned to the BP in Master Data)

The alert needs to pop up whenever a document matching these terms are created.

Regards

Bennie

[wojciech_wozniak]

Try something like this

SELECT T0.[DocNum], T0.[DocDate], T0.[CardCode], T0.[CardName], T1.[ItemCode], T1.[Dscription], T1.[Price], T1.[Currency] FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN ITM1 T3 ON T2.[ItemCode] = T3.[ItemCode]

INNER JOIN OPLN T4 ON T3.PriceList = T4.ListNum WHERE T4.[ListNum] =1 AND T1.PRICE<T3.Price

[bennie1402]

hi wojciech.wozniak

i might be doing something wrong but it doesn't work.

I saved the query and assigned it to an alert and set it to every 1 minute but it doesn't pop up alerts.

Also, when i execute the query it doesn't bring forward the desired results.

Here is a more in depth explanation of the requirement.

There are currently 16 pricelists(more to be added in future)

BP's are assigned 1 of the 16 pricelists.

When creating an invoice the unit price populates with the unit price of the assigned price list.

In many cases sales people will over ride this set price.

The alert requirement is that the document in question(ar invoice) should pop up on an alert showing the price entered vs the listed price, with the document number, the bp code and bp name and the item number, a bonus would be to drill into the document from the alert.

The alert shouldnt bring up a full report, but only the newly created document.

A separate query report with a date range selection for the same purpose will be helpful to do price analysts on historical price changes.

Regards

Bennie

[bennie1402]

We got this far but somewhere in the timestamp something is incorrect.

SELECT t0.Docentry as 'Link To Document', T0.[Docnum], T0.[doctime], T0.[createdate], T0.[CardName], T5.[SlpName], T0.[NumAtCard],T0.[DocDate],T0.[DocStatus], T0.[Printed], T1.[ItemCode],T1.[Quantity], T1.[Price] as SalesPrice, T4.[Price] as Price FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

inner join OSLP T5 on t0.SlpCode = t5.SlpCode

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN ocrd t3 on t3.cardcode=t0.cardcode

INNER JOIN ITM1 T4 ON T2.ItemCode = T4.ItemCode and t4.pricelist=t3.ListNum

WHERE T1.[Price] <> T4.[Price] and T0.[DocDate] = cast(GetDate() as DATE)

AND CONVERT(VARCHAR(2),GETDATE(),108)+RIGHT(CONVERT(VARCHAR(5),GETDATE(),108),2) >= (T0.[DocTime]-0)

AND (T0.[DocTime]+5) >= CONVERT(VARCHAR(2),GETDATE(),108)+RIGHT(CONVERT(VARCHAR(5),GETDATE(),108),2)

The results from the query is perfect but the alert triggers every 1 minute as per the setting in alert management

and it should only trigger when a document is created matching the criteria.

Any Ideas?

[former_member4944]

Hi bennie1402

Try This...

SELECT T0.[Docnum],T1.[Price] as SalesPrice, T4.[Price] as Price FROM OINV T0

INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry

INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

INNER JOIN ocrd t3 on t3.cardcode=t0.cardcode

INNER JOIN ITM1 T4 ON T2.ItemCode = T4.ItemCode and t4.pricelist=t3.ListNum

WHERE T1.[Price] <> T4.[Price] and T0.docstatus = 'O'

[bennie1402]

Hi Abdul Basith

This works thanks.

It doesnt work for the alert pop up (unless im missing something)

But for a report it gives me what i need to back into historical records (although it will pull the current unit price in the pricelist and not the unit price from past date) but this is a step in the right direction.

Regards

[bennie1402]

Hi Abdul Basith

The query works, there was an issue with our alert service layer.

Just cant figure out how to have the alert to only trigger when the criteria is met. Alert is set for every 1min and it triggers every 1min instead of only triggering when conditions are met