inventory in whse report with serial and batch with reference document

[Former Member]

HI all,

System based report only provide the quantity that's why i created this query but in addition i want the last document reference number,and document type with reference to which a item resides in that warehouse.(There are many transaction on a batch/sn credit memo/invoice/stock transfer etc.)

This is the below query

SELECT OSRN.itemName   

,'' as dc_no           

,OSRN.ExpDate AS 'Expiry Date'               

,WhsName             

,DistNumber AS BATCH_SERIAL_NO,OSRQ.Quantity              

, DATEDIFF(DAY, GETDATE(), OSRN.ExpDate)    AS 'Remain Days'                 

,(case when DATEDIFF(DAY, GETDATE(), OSRN.ExpDate) <0 then 'expired' end) remark             

,'SERIAL CONTROLLED' AS RMK   

  into #t           

FROM OSRN LEFT JOIN OSRQ             

ON OSRN.ItemCode = OSRQ.ItemCode AND OSRN.SysNumber = OSRQ.SysNumber             

inner join OWHS on osrq.WhsCode=owhs.WhsCode and Quantity<>0          

WHERE     osrq.WhsCode between ISNULL(@from_WHSE,osrq.WhsCode) and ISNULL(@to_WHSE   ,osrq.WhsCode)           

and( owhs.County=isnull(@territory,owhs.County) or owhs.county is null)           

union all         

SELECT OBTN.itemName   

,OBTN.ExpDate AS 'Expiry Date'             

,WhsName             

,DistNumber AS BATCH_SERIAL_NO             

,OBTQ.Quantity              

  , DATEDIFF(DAY, GETDATE(), OBTN.ExpDate)   AS 'Remain Days'              

,(case when DATEDIFF(DAY, GETDATE(), OBTN.ExpDate) <0 then 'expired' end) remark              

,'BATCH CONTROLLED' AS RMK             

FROM OBTN LEFT JOIN OBTQ             

ON OBTN.ItemCode = OBTQ.ItemCode AND OBTN.SysNumber = OBTQ.SysNumber             

inner join OWHS on OBTQ.WhsCode=owhs.WhsCode and Quantity<>0             

WHERE  OBTQ.WhsCode between ISNULL(@from_WHSE,OBTQ.WhsCode) and ISNULL(@to_WHSE   ,OBTQ.WhsCode)         

and( owhs.County=isnull(@territory,owhs.County) or owhs.county is null)           

ORDER BY itemName              

[BattleshipCobra]

Thanks Khagesh!

I needed only the serial portion but it seems to work.

SELECT 
          T0.[ItemCode]
          ,T0.[ItemName]
    ,T0.[DistNumber]
    ,T2.[WhsName]
          ,T1.[Quantity]
FROM 
          OSRN T0
          LEFT JOIN OSRQ T1 ON T0.[ItemCode] = T1.[ItemCode] AND T0.[SysNumber] = T1.[SysNumber]             
          INNER JOIN OWHS T2 ON T1.[WhsCode] = T2.[WhsCode] and T1.[Quantity] <> 0
          LEFT OUTER JOIN OITM T3 ON T0.[ItemCode] = T3.[ItemCode]
WHERE
          T1.[WhsCode] BETWEEN '[%0]' AND '[%1]' AND T3.[ManSerNum] = 'Y'
UNION ALL
SELECT
          T0.[ItemCode]
          ,T1.[ItemName]
    ,NULL
    ,NULL
          ,SUM(T0.[OnHand])
FROM 
          OITW T0
          INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode
WHERE
          T0.[WhsCode] BETWEEN '[%0]' AND '[%1]' AND T0.[OnHand] > 0 AND T1.[ManSerNum] <> 'Y'
GROUP BY T0.[ItemCode],T1.[ItemName]