Calculation on Lookup based on stock type

[Oyseka]

Hi all yet again. I have a requirement to differentiate the VAT elements of a product based on whether the stock was purchased for retail or is held as consignment stock. At the moment I can think of no way of doing this apart from giving the consignment stock and retail stock different part numbers which would double the part numbers and introduce a large chance of input error by the operator selecting the wrong part number for the same product.

When a purchase order is produced it is either for consignment or for retail, if it is for retail the VAT is due in the period in which it is purchased, if it is for consignment stock, the VAT only becomes due in the period in which that stock is sold. The VAT report for retail stock is simple because that comes directly from the purchases table but the problem arrises when an order is fulfilled from a mix of consignment and retail stock as the VAT for the consignment stock must come from the invoice line items and I can think of no way to differentiate which is which in the line items table. Any help would be appreciated