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.

Parents
  • 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.

Reply
  • 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.

Children