cancel
Showing results for 
Search instead for 
Did you mean: 

I need production order cost with all Production issue and production receive.

Deepak33
Discoverer
0 Kudos
111

I have created a query. But it adds the value multiple times. Kindly provide a solution.

SELECT DISTINCT T0."DocEntry", T0."DocNum", T0."ItemCode", T0."ProdName",SUM(T1."Quantity"*T1."StockPrice"),SUM(T2."Quantity"*T2."StockPrice")
FROM OWOR T0
Left JOIN IGE1 T1 ON T1."BaseEntry" =T0."DocEntry" AND T1."BaseRef" = T0."DocNum" AND T1."BaseType" =202
Left JOIN IGN1 T2 ON T2."BaseEntry" =T0."DocEntry"
WHERE T0."DocNum" = '31457'
GROUP BY T0."DocEntry",T0."DocNum",T0."ItemCode", T0."ProdName"

 

SAP Enterprise Support SAP Business One, version for SAP HANA  SAP ERP for Business All-in-One, manufacturing edition SAP Field Service Management, connector for SAP Business One SAP Business One 

Accepted Solutions (0)

Answers (2)

Answers (2)

ManishPant
Participant

Hi @Deepak33 ,

I don't know if this will work for you or not.

SELECT 
X."BaseRef", 
X."BaseEntry",
(SELECT "ItemCode" FROM OWOR WHERE OWOR."DocNum"=X."BaseRef") AS "FG_CODE",
(SELECT "ProdName" FROM OWOR WHERE OWOR."DocNum"=X."BaseRef") AS "FG_NAME",
SUM(X."FG_PRICE") AS "FG_PRICE",
SUM(X."RM_PRICE") AS "RM_PRICE"
FROM
(
SELECT T0."BaseRef",  T0."BaseEntry",  SUM(T0."Quantity"* T0."StockPrice") AS "FG_PRICE" ,0  AS "RM_PRICE"
FROM IGN1 T0 
WHERE T0."BaseType" =202
GROUP BY T0."BaseRef",  T0."BaseEntry"
UNION ALL
SELECT T0."BaseRef",  T0."BaseEntry",  0 AS "FG_PRICE",SUM(T0."Quantity"* T0."StockPrice") AS "RM_PRICE" 
FROM IGE1 T0 
WHERE T0."BaseType" =202
GROUP BY T0."BaseRef",  T0."BaseEntry"
)X
WHERE X."BaseRef"=124020350
GROUP BY X."BaseRef", X."BaseEntry"

 

Thanks.

Deepak33
Discoverer
0 Kudos

@ManishPant 

Your solution is working, thanks for the reply. How do I add date selection from IGN1?

WHERE T2."DocDate" >=[%0\] AND T2."DocDate" <=[%1\]

 

ManishPant
Participant
0 Kudos

Hi @Deepak33 ,

/* Select * From OIGN T0 Where T0."DocDate" = [%0] */
/* Select * From OWOR T0 Where T0."DocNum" = [%1] */
SELECT 
X."BaseRef", 
X."BaseEntry",
(SELECT "ItemCode" FROM OWOR WHERE OWOR."DocNum"=X."BaseRef") AS "FG_CODE",
(SELECT "ProdName" FROM OWOR WHERE OWOR."DocNum"=X."BaseRef") AS "FG_NAME",
SUM(X."FG_PRICE") AS "FG_PRICE",
SUM(X."RM_PRICE") AS "RM_PRICE"
FROM
(
SELECT T0."BaseRef",  T0."BaseEntry",  SUM(T0."Quantity"* T0."StockPrice") AS "FG_PRICE" ,0  AS "RM_PRICE"
FROM IGN1 T0 
INNER JOIN OIGN T1 ON T0."DocEntry" = T1."DocEntry"
WHERE T0."BaseType" =202 AND T1."DocDate" = [%0]
GROUP BY T0."BaseRef",  T0."BaseEntry"
UNION ALL
SELECT T0."BaseRef",  T0."BaseEntry",  0 AS "FG_PRICE",SUM(T0."Quantity"* T0."StockPrice") AS "RM_PRICE" 
FROM IGE1 T0 
WHERE T0."BaseType" =202
GROUP BY T0."BaseRef",  T0."BaseEntry"
)X
WHERE X."BaseRef"=[%1]
GROUP BY X."BaseRef", X."BaseEntry"

 

You can use like this.

Thanks.

Manish.