SAP B1 9.1 Open sales order Quantity query

Question

I need to have a query where i can see open sales order quantity (PO and In stock) but i need to see based on customer reference. i have a customer who many stores. and his order # joins a store #. so under every order there can be 100 stores at a time. Against every store there is a sales order in SAP B1 9.1 having referece like 818201-3001(PO# -Store#) so that goods can be ship according to store address. Splitting "NUMATCARD" and consolidating quantity at store level is beyond my approach.

I need to have a query which show results as attached output sheet.

Answer 1

I have this query but couldn't translate CustomerRef into rows and columns.

SELECT T0.[DocDueDate],
    T0.[CardName],
    T0.[NumAtCard],
    T0.[DocNum],
    T1.[ItemCode],
    T1.[Dscription],
    T3.[WhsCode],
    T3.[OnHand] - SUM(OpenQty) OVER (Partition By T1.ItemCode ORDER BY DocDueDate, LineNum, NumAtCard, DocNum) + SUM(T1.OpenQty) AS 'Available',
    SUM(T1.[Quantity]) AS 'PO QTY',
    SUM(T1.OpenQty) AS 'To Release'
FROM
    ORDR T0 
    INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] 
    INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] 
    INNER JOIN OITW T3 ON T2.[ItemCode] = T3.[ItemCode] AND T1.[WhsCode] = T3.[WhsCode]
WHERE
    T0.[CardCode] = 'c00192' and
    T0.[DocStatus] = 'O' AND
    T1.[LineStatus] = 'O' AND
    T0.[CANCELED] = 'N' AND
    T1.[WhsCode] = '161'           
GROUP BY
    T0.[DocDueDate], 
    T0.[CardName], 
    T0.[NumAtCard], 
    T0.[DocNum], 
    T1.[ItemCode], 
    T1.[Dscription],
    T3.[OnHand],
    T3.[WhsCode],
    T1.[LineNum],
    T1.OpenQty
ORDER BY 
    T0.[DocDueDate], 
    T0.[NumAtCard], 
    T0.[DocNum]