We are in the midst of a migration from GP to BC as well as an EDI translator migration.
Several trading partners require that we express the tax details at the item level.
We charge both provincial and federal taxes.
We are having trouble finding this information in BC. The goal is to create a custom API which our EDI software can call to grab the relevant tax information for invoicing.
So far I can only see the taxes at the header level.
To be clear, if an invoice has 3 items on it, I want to know how much provincial and federal taxes are charge for each item.
Thank you in advance.
Hi David,
You’ll find the info you need from the VAT Entry table.
Your starting point should be the Sales Invoice Line table (for API) but your code will need to cross reference to the VAT Entry table.
To get familiar with some of the numbers, take a posted sales invoice, with both PST and GST. Hit Find Entries in the menu of that invoice. You should see VAT Entries and Item Ledger Entries, among a host of other tables this invoice posted to. Find Entries is the most useful tool you’ll find in “drilling” into ANY entry.
Hopefully, this gets you what you need.
Regards,
Hi @David_Allan
We built something very similar for a customer last year, GP to BC migration with EDI for invoice processing, same requirement from trading partners.
BC does calculate tax at the line level internally, but it just doesn’t surface it in standard APIs. The data you need lives in VAT Entry (table 254), linked via Document No. to Sales Invoice Line (table 113). Since you have PST + GST, you’ll have two VAT posting groups, filter by those to split provincial vs federal per line.
From there, build a custom API Page in AL, and join those two tables on Document No. + Line No., and expose it as JSON for your EDI software to call.
Just make sure your VAT Posting Setup is cleanly separating provincial and federal before you start, that config being off will cause headaches later.
Happy to help if you get stuck.
For everyone’s information, we have decided to calculate the line level tax breakdown when the invoices hit the EDI translator. We have 3 or 4 trading partners who require that level of detail, so it’s a small subset. Thanks for all the input.