Hi Experts,
Our Client is running SAP Business One version for HANA. I had been given a query of Opening & Closing Balance in an SCN post I had done last year. I have tweaked it a bit.
When I ran the query in SAP B1, it worked well. However, when I tired to add it to Crystal Reports command, it brought an error because my query had date parameters (From Date, ToDate & vGroup). So I created/ Declared the 3 parameters in the Parameter list and incoporated them in the query.
Where can I set the minimum value for FromDate and Maximum value for ToDate in the query? See Query Below:
Select a."ItemCode", max(a."Dscription"), sum(a."Opening Balance") as "Opening Balance", sum(a."IN") as "IN", sum(a."OUT") as "OUT",max(a."Price") as "Price", ((sum(a."Opening Balance") + sum(a."IN")) - Sum(a."OUT")) as "Closing" from "TEST".OITM I1
Left JOIN (Select N1."ItemCode", N1."Dscription",N1."Price", (sum(N1."InQty")-sum(N1."OutQty")) as "Opening Balance", 0 as "IN", 0 as "OUT"
From "TEST".OINM N1
Where (N1."DocDate") < {?FromDate}
Group By N1."ItemCode",N1."Dscription",N1."Price"
Union All
Select N1."ItemCode", N1."Dscription",N1."Price", 0 as "Opening Balance", sum(N1."InQty") as "IN", 0 as "OUT" From "TEST".OINM N1
Where N1."DocDate" >= {?FromDate} and N1."DocDate" <= {?ToDate} and N1."InQty" >0
Group By N1."ItemCode",N1."Dscription",N1."Price"
Union All
select N1."ItemCode", N1."Dscription",N1."Price", 0 as "Opening Balance", 0 as "IN", sum(N1."OutQty") as "OUT" From "TEST".OINM N1
Where N1."DocDate" >= {?FromDate} and N1."DocDate" <={?ToDate} and N1."OutQty" > 0
Group By N1."ItemCode",N1."Dscription",N1."Price"
) a
ON a."ItemCode"=I1."ItemCode"
Where I1."ItmsGrpCod" = {?vGroup} and I1."ManBtchNum" = 'Y'
Group By a."ItemCode"
Order By a."ItemCode"
Kind Regards,
Pascale