cancel
Showing results for 
Search instead for 
Did you mean: 

Can we create a field for Variance?

Former Member
0 Kudos

Hi Experts,

My client wants to see the following in SAP Business one 2007A PL:08

1 - A variance column that will calculate the difference between the A/P Invoice Price and the PO price. This could be a dolar amount or a %. How do I do this? Can I define an UDF for this?

2 - A flag at AP invoice stage where it will alert saying that the A/P invoice price is different than PO price. How do I create alerts?

3 - Lock certain fields in the header information of Item Master Data, Business Partner Master Data etc. How do Lock a specific field. Users are changing too much of information randomly.

Thanks

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Arun.....

Off course you can manage all these in UDFs.

Create Two UDFs at AP Invoice Row Level one for Price and other for Percent.....

Apply FMS at PO Price level in Prices UDF so tht Price in UDF at PO level will be carried out to AP Invoice......

then you can compare.

For Alert you can create query based alert and assign it to Alert management......

Regards,

Rahul

Former Member
0 Kudos

Hello Experts,

We use Business One 2007A. Sorry for the confusion. Further to my question above, the user wants a report that will display Vendor Code, Vendor Name, PO Number, PO Price, A/P Invoice Number and A/P Invoice Price. I was trying to create a query with the help of query generator but was unable to do so as I am not an SQL expert. I want to know the exact SQL query to pull this information so that I can save the query and the user can export this information in an excel and compare the variance in PO and A/P Invoice price.

Thanks

Former Member
0 Kudos

Hi,

The query depends on whether your business process involves GRPO or not.

Thanks,

Gordon

Former Member
0 Kudos

Yes Gordon. We do have a Business Process of GPRO.

kvbalakumar
Active Contributor
0 Kudos

Hi Arun,

Try this

SELECT distinct T0.[CardCode], T0.[CardName], 
T4.[ItemCode], T4.[Dscription], 
T5.[DocNum] [PO#], T4.[Price] [PO Price], 
T3.[DocNum] [GRPO#], T2.[Price] [GRPO Price], 
T0.[DocNum] [AP Inv#], T1.[Price] [AP Inv Price] 
FROM OPCH T0  
INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry 
inner join PDN1 T2 on T2.DocEntry = T1.BaseEntry and T2.LineNum = T1.BaseLine and T1.BaseType = '20'
INNER JOIN OPDN T3 ON T2.DocEntry = T3.DocEntry 
inner join POR1 T4 on T4.DocEntry = T2.BaseEntry and T4.LineNum = T2.BaseLine and T2.BaseType = '22'
INNER JOIN OPOR T5 ON T4.DocEntry = T5.DocEntry

For Browse

Regards,

Bala

Former Member
0 Kudos

Hi Rahul,

What is FMS?

Former Member
0 Kudos

Hi Bala,

Thanks a lot!! It worked like a Miracle!!

Answers (1)

Answers (1)

Former Member
0 Kudos

(a) Create a UDF at Purchase Order line and use a FMS to copy the 'price' into the UDF.

(b) Create a UDF at Purchase Invoice line and use a FMS to calculate the difference between the Invoice Price and PO Price

PS. The UDF that keeps the PO price will be automatically copied into the Invoice. The 'variance' UDF will contain a formula that deducts the PO price from the Invoice Price. Should you wish can can have an additional UDF that calculates the % difference.

George Z