Reg: A/R Invoice, Last Invoice price should be displayed in UDF

[former_member237214]

Dear All,

My Client asking below scenario.  Kindly advise me how to do this..

In A/R Invoice, we need to create one UDF field at the row level, in that last Invoice Price should be displayed..

I have created UDF filed at row level.. Now i wanted to display last invoice price for that particular item..

can anyone help me to do this....

Thanks..

[agustin_marcoscividanes · Accepted solution]

Hi

try this SQL query:

select top(1) T1.Price from inv1 T1 inner join OINV T0 on T0.docentry = T1.DocEntry where t1.ItemCode = $[$38.1.0] order by T0.docdate desc

Kind regards.

Agustín Marcos Cividanes

[former_member237214 · Accepted solution]

Hi,

Thanks for your reply, I have executed above mentioned query, it shows syntax error.. Please find the below error msg.

[Former Member · Accepted solution]

In this query you have to open the A/R invoice and select the item first, after that you can execute the query.

[former_member237214 · Accepted solution]

Hi,

I did the same..  But I have Received same error msg..

[Former Member · Accepted solution]

Can you try this one.

select top(1) T1.Price from inv1 T1 inner join OINV T0 on T0.docentry = T1.DocEntry where t1.ItemCode = $[$38.1.1] order by T0.docdate desc

[former_member237214 · Accepted solution]

It gives same Error..

[agustin_marcoscividanes · Accepted solution]

Hi

you cannot run this sql query in the query manager.

You have to save it, and assign to the UDF.

Kind regards

Agustín Marcos Cividanes

[former_member237214 · Accepted solution]

Thanks you so much for guide, it works but is it possible to make bit alteration,

Customer: A,

Item : X

price : 100

Customer : Y

Item : X

Price : 150

If i select Customer A, then the price should display 100, if i select Customer Y then the price should select 150..

Thanks in Advance..

[Former Member · Accepted solution]

Then you need to exand the where parametres which is,

select top(1) T1.Price from inv1 T1 inner join OINV T0 on T0.docentry = T1.DocEntry where t1.ItemCode = $[$38.1.1]  and  T0.CardCode = $[$4.0.0] order by T0.docdate desc

[former_member237214 · Accepted solution]

Hi,

Thank you so much for your valuable reply..   It works fine, Client asking to popup System alert message window, If current Invoice price lesser than previous invoice price, then system should alert me, " Previous Invoice Price is high"..  Is It possible to make custom System Alert message..

Thanks ..

[Former Member · Accepted solution]

If you want to stop the current user who is adding the document you can use the Transaction_Notification procedure.

But in this is not the alert message it shows below the screen which is like the general SAP B1 error.

If you want to error pop-up I think you need to create add-on for that. But maybe you can send this query from the using alert message or you can use the Approval Templates.

[KennedyT21 · Accepted solution]

Hi Logesan...

You are allowed to post one question per thread , i think your question is answers here mark the correct and helpful answer and open a new thread for new questions...

You should follow the rules of engagement...

Cheers!

[Former Member]

Hi Logesan,

You don't need to open UDF for that.

When you select the item on A/R Invoice after that rigth click on your mouse and select the Last Prices.

It shows the last prices of item.And you can select the BP and document type. An

[former_member237214]

Hi,

Thanks for your response..  Sales Department manager wants to see at the row level only, they wanted to check the Current Sales Invoice price and Last Invoice price at the time of approval, is there any way to pull that price at row level?

[Former Member]

Hi Logesan,

You cannot fetch data in row level for UDF fields but you can put FMS query over there and all previous invoices you will get.