Re: Over receiving items on purchase orders

[Ed W <..hidden..>]

Hi

If you are using shipping or something then I don't know how that will work - 
we found that SL's shipping/receiving caused very weird things to happen and 
am sure it has destroyed our onhand values over time so we only use p/o's and 
direct conversion to vendor invoices.
    

Good to know.  Sounds like I've either run across a bug or I'll need to 
help the client adjust their processes.
  

I reported this to Dieter and he denied that the problem existed.  Then I notice that there is a fix for something similar sounding in the new SL Changelog...

The Changelog reference suggests that the problem occurs when shipping is used, invoice is raised, then the original order is deleted (or I wonder if perhaps it also occurs on a change...?)

....Perhaps someone would be interested in fixing this in the current SMB codebase because it would be extremely useful to have working shipping functionality which doesn't bugger up the onhand...

As an aside the following query shows the onhand values as of a given date.  It would be very useful if this were a standard report on the system (eg for doing inventory checks and certain end of year/quarter reports)

Ed W


select id, partnumber, description, onhand, sold, bought, -bought+-sold as net
from
  (select *,
    (select COALESCE(sum(i.qty), 0)
    FROM invoice i
    JOIN ar a ON (a.id = i.trans_id)
    where i.parts_id = p.id
    and transdate <= '2007-03-31') as sold,

    (select COALESCE(sum(i.qty), 0)
    FROM invoice i
    JOIN ap a ON (a.id = i.trans_id)
    where i.parts_id = p.id
    and transdate <= '2007-03-31') as bought

  from parts p
  where assembly=false
  ) as foo
where bought+sold <> 0
order by description