cancel
Showing results for 
Search instead for 
Did you mean: 

Bin location wise stock report SAP Business One

nikunjmehta2290
Participant
0 Kudos
438

Hello all,

Please help me on Bin location wise stock report from SAP Business One HANA.

Regards,

Nikunj

View Entire Topic
MukeshSingh
Participant
0 Kudos

Try this - Change as per HANA

/* select docdate from B1_OinmWithBinTransfer t56 */ declare @Fromdate as datetime declare @todate as datetime set @fromdate = /* t56.docdate */ {?fromdate} set @todate = /* t56.docdate */ {?todate} SELECT T1.ItemCode, T1.Dscription, T3.WhsCode, T3.BinCode, sum(case when T1.DocDate<@fromDate then (case when t1.InQty <> 0 then (case when T2.Quantity is null then t1.InQty else T2.Quantity end) else 0 end) else 0 end)- sum(case when T1.DocDate<@fromDate then (case when t1.OutQty <> 0 then (case when T2.Quantity is null then t1.OutQty else T2.Quantity end) else 0 end) else 0 end) as 'OpenQty', sum(case when T1.DocDate>=@fromDate and T1.DocDate<=@todate then (case when t1.InQty <> 0 then (case when T2.Quantity is null then t1.InQty else T2.Quantity end) else 0 end) else 0 end) as 'InQty', sum(case when T1.DocDate>=@fromDate and T1.DocDate<=@todate then (case when t1.OutQty <> 0 then (case when T2.Quantity is null then t1.OutQty else T2.Quantity end) else 0 end) else 0 end) as 'OutQty', sum(case when T1.DocDate<=@todate then (case when t1.InQty <> 0 then (case when T2.Quantity is null then t1.InQty else T2.Quantity end) else 0 end) else 0 end)- sum(case when T1.DocDate<=@todate then (case when t1.OutQty <> 0 then (case when T2.Quantity is null then t1.OutQty else T2.Quantity end) else 0 end) else 0 end) as 'CloseQty' FROM B1_OinmWithBinTransfer AS T1 left JOIN OBTL AS T2 ON T1.InvPLMessageID = T2.MessageID left JOIN OBIN AS T3 ON T3.AbsEntry = T2.BinAbs WHERE T3.BinCode <>'' group by T1.ItemCode, T1.Dscription, T3.BinCode, T3.WhsCode