Query to get sales profit with cost calculated adding variance amount sap b1

[former_member149698]

Dear Team,

I am looking for a query for sales report with cost calculated adding varaince amount also in sap b1. Here costing method is FIFO and batch is enabled. Please help me to solve the query.

Thanks

Vishnu Sivanandhan

[Abdul]

Hi there,

Plesae follow community guidelines and share more details of your problem and steps you did to resolve those problems.

[former_member149698]

Dear Team,

To calculate cost with price variance occurred , i created a query like this

(select sum("Tempprice")/T1."InvQty" from (

select

(Case when SUM(A."InQty")<=0 then 0 else ((sum(A."OpenStock")+sum(A."PriceDiff"))/sum(A."InQty")) end)* C."LayerOutQ" as "Tempprice"

from OIVL A

INNER JOIN OIVQ B ON B."TransSeq"=A."TransSeq" and A."TreeID"=(Select MIN("TransSeq") from OIVQ where "TreeID"=B."TreeID")

INNER JOIN OIVE C ON B."TreeID"=C."EntryTreeI"

INNER JOIN OIVL D ON C."TransSeq"=D."TransSeq"

where

D."TransType"= ( case when T1."BaseType"<>'15' then T1."ObjType" else T1."BaseType" end )

and D."CreatedBy"=(case when T1."BaseType"<>'15' then T1."DocEntry" else T1."BaseEntry" end )

and D."DocLineNum"=(case when T1."BaseType"<>'15' then T1."LineNum" else T1."BaseLine" end)

GROUP BY C."LayerOutQ",C."LayerID")) end,2)

as "cost"

I am able to get all cost with price varaiance but one scenario is missing. System is not considering Inventory revaluation price in this query.

Kindly help me to add that condition in this query