There is a warehouse program. Interbase 2009. In the database there are tables Income (receipt), Outcome (consumption) .....

Income :

  • Cost (cost price)
  • Vat (VAT, which I pay upon purchase),
  • IncomeDate (date of arrival),
  • Qty (ward count),
  • QtyCurr (qty now).

Suppose 01/11/2016 is registered as a VAT payer. But at this moment there are remnants of the old parishes. In the old records, on which there are balances for those goods that are subject to VAT, if you change the cost figures and VAT, for example, if the price was 2 dollars. and was distributed like this:

Cost-2; Vat-0

will become now

 Cost-1.695; vat-0.305.

But then the profit from old sales (until 01/11/16) will be calculated incorrectly, instead of (sales. Price without VAT-2) will be (sales. Price without VAT-1,695)!

What changes can be made in the database, and not in the requests to get around this problem?

There are several options:

  1. Write off the old balance with 0 VAT and accept the same amount with VAT and other cost price? But then in the table “cap” of the parishes for each company there will be 2 entries (the first-old and the second-new arrival after writing off the balance), and the arrival was only one.

  2. In the database add a table (VatVersion) with a period of time. that is, the final purchase price (InPrice), cost price and VAT will remain in the table of income records (Income).

     Income: Income_id,IncomeDate, cost, vat, Qty, InPrice... VatVersion: id,StartDate, EndDate,VatPayable(0 or 1).

    But then it turns out that for the old balances in Income I continue to store the goods with the old cost and zero VAT !!!

  3. Do not store in the cost price and VAT, and count in the program. To do this, create a table as in option 2 with periods of time and the taxation flag (spends -1, does not spend-0).

Criticize these options and offer another solution if there is one.

  • Create a table of "versioning" with the date and VAT on this date, respectively, spend all the calculations using this table - JVic
  • @ Victor, thanks for the reply. Read, please, option 2. If you had this in mind, then it turns out that the old balances in Income continue to store the goods with the old cost and zero VAT !!! For example, the cost price was 10 and VAT 0, and after getting registered, since vatpayable = 1, the cost price for the sale of 8.47 and VAT 1.53. And in the database, the old prices are kept vseravno- 10 and 0 !!! - Delphi159

0