Hello!
I have following query:
| Code |
|---|
SEL ECT DISTINCT InventoryJrnlBalanceAndTurnovers.QtyOpeningBalance AS QtyOpeningBalance, InventoryJrnlBalanceAndTurnovers.Product AS Product, InventoryJrnlBalanceAndTurnovers.QtyClosingBalance AS QtyClosingBalance, InventoryJrnlBalanceAndTurnovers.QtyExpense AS Sold, InventoryJrnlBalanceAndTurnovers.Product.Description AS ProductDescription2, InventoryJrnl.Recorder, InventoryJrnlBalanceAndTurnovers.QtyReceipt - CASE WHEN VALUETYPE(InventoryJrnl.Recorder) = TYPE(Document.Adjustment) THEN InventoryJrnl.Qty ELSE 0 END AS AdjustedPurchase, CASE WHEN VALUETYPE(InventoryJrnl.Recorder) = TYPE(Document.Adjustment) THEN InventoryJrnl.Qty ELSE 0 END AS QtyAdjusted, InventoryJrnl.Period FR OM AccumulationRegister.InventoryJrnl.BalanceAndTurnovers(&BeginOfPeriod, &EndOfPeriod, , , ) AS InventoryJrnlBalanceAndTurnovers, AccumulationRegister.InventoryJrnl AS InventoryJrnl WH ERE InventoryJrnl.Period >= &BeginOfPeriod AND InventoryJrnl.Period <= &EndOfPeriod AND InventoryJrnl.Product = InventoryJrnlBalanceAndTurnovers.Product |
There is one document "Adjustment" for corrections of quantity. I need to extract this quantity fr om purchase.
For example there were 10 peaces purchased, 7 sold, but 4 were lost. So the user put -3 peaces in the document adjustment. QtyClosingBalance is 10-7-3 = 0.
But after this query for products that had adjusted quantity I get two lines in the report:
Product | BegBalances | Purchased | Sold | ClosingBalance | QtyAdjusted
-----------------------------------------------------------------------
Product1 | 0 | 600 | 577 | 0 | -23
Product1 | 0 | 577 | 577 | 0 | 0
Product2 | 235 | 0 | 210 | 25 | 0
As you can see Product1 had an adjustment -23, that's why there are two lines. I need only one line for each product.
Thank you for any advise!
