We have set up GST application where I am getting difference between reports like monthly item wise sales report against monthly total sales report.
Please suggest me any changes I need to make for following rules:
We have 3 tables A. Order master - single line for every order is stored with summarized values B. Order product details - Each line item stored with respective qty, price etc. C. Order tax details - line item wise tax details having separate row for CGST, SGST etc.
Here is how I am storing the data:
- Tax is applicable on every line item [To generate final invoice all items in order group, we create some of all line items and storing value in Order Master.]
- We are rounding 2 decimal value for 3 precision [eg. 4.657 = 4.66, 4.643 = 4.64] as per GST council
- Rounding value is stored in Order Master table
- For discount there are 2 cases
- Percent based discount - Let's say if discount is 10% then from every individual line item 10% discount gets deducted than tax will be applicable for each line item
- Flat discount - Let's say for 1000/- Rs order somebody wants to give 80/- Rs. discount than from each line item 8% is deducted and than tax will be applied, off course customer may not pay exact 920/- Rs. amount due to reversal of tax amount.
Now when I am generating following reports:
- Monthly item wise sales report [Product wise sales report]
- Monthly total sales report [Total sales]
- Monthly payment type wise report [Bank, Cheque, Cash]
I am getting difference due to rounding of values. Can anyone suggest me which is the best way to set up rounding formula and up to which decimal point should I go to round the values.
I am using SQL server as backend and .net as front end technology.
