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!
