Get all active Sales Taxes

Hi,

I am trying to get all active Tax Codes and iit's taxes using .NET SDK.

From tTaxAuth table, I can get all Taxes ,from tTaxCode I am able to get all taxcodes and from tTxCdDtl table I am able to get Tax rate for each Taxcode's tax.

I have removed on Tax from Taxcode listing but still I can see into tTxCdDtl table using SDK and I didn't found any specific status which say its deleted or inactive.

May I know, how I can identify particular tax has been deleted from Taxcode?

Thanks for your help.

  • I cannot respond directly to your query about identifying tax codes that have been deleted but I can suggest that this practice would not be recorded as the users do not delete tax codes. The only safe way to delete a tax code would be if all of the historical lookup data is also deleted. Once a code is deleted if it is used in an historical entry then the entry would show incorrectly and all historical reports would be incorrect as well. Deleting data and codes from the Sage 50 files is not a very wise move from the users point of view.

  • Nil

    This is a very complicated part of the database and I am going to contradict some of what Alwyn has written (which is not normally ever done).  There is no need usually to delete tax codes as he indicated but the program will not fail because of deleting a code.  Past entries will still display and calculate correctly.

    When you are working with the tax codes, you must always consider either the `lLastRev` field in any of the tables or both the `lFirstRev` and `lLastRev` if available and depending on your requirements.

    For example, if your highest `ttaxcode`.`lLastRev` is 60 and you see a tax code that the highest `ttaxcode`.`lLastRev` is 59, then it was deleted at some point and is no longer available on current transactions.

    If `ttxcddtl`.`lLastRev` has multilple revisions for the same `lTaxId`, then it is possible it was edited.  It is possible to change a rate eg. from GST 7% to GST 5% and create a new revision.

    If you delete a tax code, then it is no longer available for use.  However, contrary to what Alwyn indicated the past entries that used that tax code will still calculate and show the proper calculation for that tax code for that entry and if you edit that entry you will still see that tax code available and calculating at the rate at that time.  And by time, I do not mean as of a specific date of entry, but as of the time the calculations were changed.

    Examples (only do this on a copy of a file until you know what you are doing - you can track the revisions between or after the below steps as you feel necessary - I can't tell you what the revision numbers are because they are different for each database).

    Using tax code G calculated at 5% you can create a sales invoice for $100 which will total $105.  Date it Jul 31/19.

    Then change the tax code calculation so it now calculates at 10%.

    Using tax code G now calculating at 10% you can create a sales invoice for $100 which will total $110.  Date it Jun 30/19.

    Now delete tax code G.

    At the same time create tax code W using the GST tax and give it any calculation you want.  I will use 5%.

    Using tax code W calculated at 5% you can create a sales invoice for $100 which will total $105.  Date it May 31/19.

    Now the fun part.

    Go back and edit the first invoice.  On a second line add a dollar amount and pick tax code W.  Do you have one?  For those reading and not doing, the answer is No.

    However, when you add a new line with tax code G, it calculates properly at 5% because at the time of the original entry, that was the calculation and the sales tax code that was available.  The 10% G calculation is not available.

    Now edit the second invoice.  It now calculates G at 10% on new lines as well as the original, again because that was the calculation at the time of the entry even though this invoice is dated a month before the original you created above.  Is W available?  Shouldn't be.  Is 5% available as a G tax code?  No.

    Now edit the third invoice.  Is G available?  Even though this entry was dated before the other two G invoices?  No.

    For each sales and purchase invoice `titluli`.`lTaxRev` keeps track of the revision of the tax code and the program pulls this information when displaying past entries.  How you pull that information is highly dependent on your purpose and informational needs but this should get you going as long as you have a strong understanding of SQL syntax and capabilities.

  • Hi Richard, I don't mind being corrected but I do appreciate your extra comment. This may be the case because I got my training in depth by one who I consider an expert. I always appreciate your technical comments dealing with the tables and their interconnection as this is beyond me. Your explanations are great.

  • Hi Alwyn,

    Thanks for your detailed answer.

    Yes, I can see the deleted tax code's number is not updated to the latest lLastRev Number and I have got the desired result.

    Thanks a lot!

  • Hi Richard,

    Thanks for your detailed answer.

    Yes, I can see the deleted tax code's number is not updated to the latest lLastRev Number and I have got the desired result.

    Thanks a lot!