Forum

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!