ONESOURCE Indirect Tax Integration for SAP Reconciliation of Tax Data Table to G/L Entries

Often system users ask us how they can tie or reconcile the information that is stored in the tax data table to the G/L entries in SAP. Currently there is no 100% accurate solution to link G/L document (BSEG) line items to the tax data table (/IDT/D_TAX_DATA) line items. This is true because the tax data table line number field (/IDT/D_TAX_DATA-DOC_LINE_NUMBER) is based on the transaction data as it exists at the time of tax calculation. In many scenarios SAP’s code changes this item numbering before it saves the G/L document.
This is because the /IDT/D_TAX_DATA table info is coming from Determination and uses the document line numbers from the original transaction document and will differ based on what document type is being processed, etc. Documents that are posting to either a customer or vendor account normally assign it to the first line of the document. The lines following are from the resulting line items on the original document and are used by the tax engine to structure the returned data from Determination. This can quite often match how the entry is posted in the G/L document for simple document structures, but will differ in many more complex scenarios where there may be multiple customer/vendor accounts, cross company scenarios, FI entries that do not post to a customer or vendor, etc.
The G/L entry side however also has a problem in that the summarization function adds together many lines that have the same account key and tax code into one line after the calculation has occurred. This is standard sap logic to summarize the lines on the accounting document and cannot be easily avoided. A user could turn off the summarization but would run into problems with large documents that max out the 999 line item limit on the G/L document. (Something most companies would not want to do because of their business models). Additionally this is also dependent on how the customer has assigned tax codes and account keys to the account assignment process. The more granularities in assignment (such as by State in US), the less summarization that occurs within the G/L entry, and likewise, the fewer granularities in tax code assigns, the more summarization occurs. So trying to reconcile the data from the tax data table to the G/L lines is not an easy or possible feat at the detail level. They cannot do this level of reconciliation due to standard SAP logic. It is unavoidable and not a one-to- one reconciliation but instead a one-to-many issue with many options and logic paths.
In the prior Integration within our 5.X Integration versions there was only one tax code and account-key that was assigned for the most part. This would result in the reconciliation process to be easier and effectively would result in a quasi-document level process based on jurisdiction levels rather than at the line item level. However down-stream use of the data in reporting became more troublesome. Also note that within the Determination system, the reconciliation report was designed and offered to customers as a way to reconcile the G/L to the Audit Database. This feature is provided based on the total tax for the whole document and is not attempted at the line item level within a given document for the very same reasons explained above.
The recon extract and Determination recon report only pulls summary totals for the document overall and compares them. Because of the G/L summarization of like lines on the accounting doc that is as far as they can go and cannot go further down to a line by line recon without preventing summarization of the accounting docs. System users look to using the tax data table for their reporting needs and likely ask these questions as a way to reconcile data without using the recon extract and Determination recon report if they have not purchased reporting or compliance modules. What they must understand and adjust in their process design is that they need to reconcile based on total taxes reported by document between the tax data table and the General Ledger rather than trying to force a line item level reconciliation.