Production order item availability

[Yulolo14]

Hello guys

i need a query from production order that will show the item planned qty against the qty available from 3 warehouses. Query looks something like this.

I was able to do it but the warehouses are aligned vertically and it doubling the item number so as its Qty.

This is the code I used. 

SELECT T0."DocNum", T1."ItemCode",T1."PlannedQty", T2."WhsCode", T2."OnHand",T0."PostDate", T0."StartDate", T0."DueDate", T0."Status" FROM OWOR T0 INNER JOIN WOR1 T1 ON T0."DocEntry" = T1."DocEntry" INNER JOIN OITW T2 ON T1."ItemCode" = T2. "ItemCode" WHERE T0."Status" = 'P' and T2."WhsCode" IN ('RM','FG') and T0."StartDate" >= [%0] and T0."DueDate" <= [%1]

Item here is being double.

Looking forward for sap community help.
thanks in advance guys.  

[Yulolo14]

hello sap community.. any recommendation on how i can achieve the query?

[neilos]

Hi, I don't consider myslf an expert with SQL, but looking at this initially seems logical until you try to figure out how to get the OnHand quantity from each warehouse.

You'll need to add a sub query for each warehouse to return the quantities for each ItemCode in separate columns.
Where this is tricky, each warehouse sub query will need to know which itemcode to check the quantity.
Sub queries don't use any data from parent queries, unless I can be proved wrong? And I can't think of a way to join the sub queries to only check against the ItemCodes returned from the parent query.

Edit: Posted prematurely

[SonTran · Accepted solution]

Hi,

You can refer this topic to convert from line to column.

https://forum.sapb1.vn/community/saphana/cach-pivot-ket-qua-truy-van-trong-sap-hana/#post-154

Hope this helps,

Son Tran

[Yulolo14 · Accepted solution]

hello.. this wouldn't work as i need also data from OITW to match to the itemcode. someone told to use subquery but i encounter an error...

[Yulolo14 · Accepted solution]

this is the code: SELECT T0."DocNum" , T1."ItemCode" , T1."PlannedQty" , T1."wareHouse" , (SELECT OnHand FROM OITW WHERE WhsCode = 'RM' AND ItemCode = T1."ItemCode") AS "OnHand\_RM" , (SELECT OnHand FROM OITW WHERE WhsCode = 'FG' AND ItemCode = T1."ItemCode") AS "OnHand\_FG" , T0."PostDate" , T0."StartDate" , T0."DueDate" , T0."Status" FROM OWOR T0 INNER JOIN WOR1 T1 ON T0."DocEntry" = T1."DocEntry" WHERE T0."Status" = 'P' AND T1."wareHouse" IN ('RM', 'FG') AND T0."StartDate" >= [%0] AND T0."DueDate" <= [%1]

SAP returns with an error. "1). [SAP AG][LIBODBCHDB DLL][HDBODBC] Syntax error or access violation;257 sql syntax error: line 1 col 8 'Received Alerts' (OAIB) (at pos 8)"

Maybe you can help me out to dedug the query... thanks a lot bro..

[SonTran · Accepted solution]

Try this

select T0.DocEntry, T0.StartDate, T0.DueDate,T0.Status, T1.ItemCode, T1.PlannedQty, 
sum(case when T2.WhsCode='RM' then T2.OnHand else 0 end) as 'OnHand_RM',
sum(case when T2.WhsCode='FG' then T2.OnHand else 0 end) as 'OnHand_FG'
from OWOR T0 join WOR1 T1 on T0.DocEntry=T1.DocEntry and T1.ItemType=4
left join OITW T2 on T1.ItemCode=T2.ItemCode and  T2.WhsCode in ('RM', 'FG')
where T0.Status ='P' and T0.StartDate>=[%0] and T0.StartDate <=[%1]
group by T0.DocEntry, T0.StartDate, T0.DueDate,T0.Status, T1.ItemCode, T1.PlannedQty

[Yulolo14 · Accepted solution]

Hello SonTran,

the code works.. but can i ask what is the role of T1."ItemType"=4 ?

[SonTran · Accepted solution]

There are 2 component types in production order:

• 4: Item
• 290: Resource

[Yulolo14 · Accepted solution]

I see... thanks for the help bro... you are a life saver...

[neilos · Accepted solution]

Thats a great solution