AR Invoice Unit price/Discount change

[former_member186803]

Hi Experts,

I need your help regarding this....

In Sales A/R process of SAP B1 8.82, we first do sales order then copy to AR Invoice to process the invoice.

At some time users do the sales order and then copy to AR invoice and modify either unit price or discount as per the customer's requirement.

I need a query to get all these type of invoices where there is a either unit price change or discount change.

I found that any AR invoice update goes to ADOC and ADO1 table, but this table populates only when the AR invoice is saved once and then opened again and modify. but if the unit price or discount has been changed at first and saved then these table does not populate.

So how can I get this changes log, from which table so that I can frame the query.

Your assistance is highly appreciated.

Rgds

Suman

[Former Member · Accepted solution]

Hi Suman,

Try:

SELECT T0.[CardCode], T0.[CardName], T3.[DocNum] as 'Sales Order No', T3.[DocDate] as 'Sales Order Date',

T0.[DocNum]as 'Invoice No',T0.[DocDate] as 'Invoice Date', T1.ItemCode, T1.[Price] 'Price from order', T2.Price 'Price from invoice',  T1.[DiscPrcnt] 'Discount from order row',T2.[DiscPrcnt] 'Discount from Invoice row' ,T0.[DiscPrcnt] 'Discount from order', T3.[DiscPrcnt] 'Discount from Invoice'

FROM OINV T0

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

INNER JOIN RDR1 T2 ON T1.[BaseEntry]= T2.[DocEntry] and T2.[TargetType] =13 AND T1.BaseLine=T2.LineNum

INNER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry

WHERE T1.[Price] <> T2.[Price]  AND T0.CardName Like '%[%0]%'

Thanks,

Gordon

[former_member186803 · Accepted solution]

Hi Gordon and All

Thanks for your reply and help.

Gordon

Your query gives me the output which is not giving correct information, price for order and price from invoice are same but in your query it shows different.

Anyway, I am going through this and let you know if get any solution.

Rgds

Suman

[former_member186803 · Accepted solution]

Hi Gordon,

I have got the solution through this query...

SELECT T0.[DocNum],  T0.[CardCode], T0.[CardName], T1.[ItemCode],   T3.[PriceBefDi] ordrprice, T3.[DiscPrcnt] ordrdisc,T1.[PriceBefDi] invprice, T1.[DiscPrcnt] invdisc

FROM [dbo].[OINV]  T0

INNER JOIN [dbo].[INV1]  T1 ON T0.DocEntry = T1.DocEntry

inner join ORDR T2 on t2.docnum=t1.baseref

INNER JOIN RDR1 T3 ON T2.DocEntry = T3.DocEntry and 

t1.itemcode=t3.itemcode

WHERE T0.[DocDate] >=[%0]

AND T1.BaseLine=T3.LineNum

and  (T1.[Price] <> T3.[Price] or  T1.[DiscPrcnt] <> T3.[DiscPrcnt])

Thanks to all

[kothandaraman_nagarajan]

Hi,

If sales order is copied to AR invoice, system does not allow you to change unit price and discount fields. In this case there is no use of ADOC table.

You have to compare sales order unit price and discount field with AR invoice. Try above queries posted members.

Thanks & Regards,

Nagarajan

[former_member186803]

Dear Nagarajan,

your post .."If sales order is copied to AR invoice, system does not allow you to change unit price and discount fields. In this case there is no use of ADOC table."

Is this correct? I can change price and discount on AR invoice even if I copy it from sales order.

I don't know whether I am getting you correctly or not.

Rgds

[kothandaraman_nagarajan]

Hi,

Sorry for incorrect information. Yes we can change unit price and discount before adding.

Thanks & Regards,

Nagarajan

[Former Member]

hi Suman,

try below query

SELECT T0.[CardCode], T0.[CardName], T3.[DocNum] as 'Sales Order No', T3.[DocDate] as 'Sales Order Date', T0.[DocNum]as 'Sales Invoice No', T0.[DocDate] as 'Sales Invoice Date' FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN  RDR1 T2 ON T1.[BaseEntry]= T2.[DocEntry] INNER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry WHERE T2.[TargetType] =13 and  (T1.[Price] <> T2.[Price] or  T1.[DiscPrcnt] <> T2.[DiscPrcnt] or T0.[DiscPrcnt] <> T3.[DiscPrcnt]) GROUP BY T0.[CardCode], T0.[CardName], T3.[DocNum], T3.[DocDate] , T0.[DocNum], T0.[DocDate]

regards,

Raviraj