Skip to Content
0
Former Member
Aug 01, 2012 at 08:07 AM

inventory in whse report with serial and batch with reference document

233 Views

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