Administration, Core Financials, and Project Accounting

[oyama]

I receive a request to show on the invoice at every line whether or not the line item got charged tax. Currently, we mark a line whether or not the item is taxable. However, it does not mean we actually charged tax on that item.

So, right now when we look at the invoice we cannot tell how the final invoice tax amount was calculated, which line items were taxed. Can it be shown which line item was taxed and how much?

[tbrasel]

You can add that via Crystal Reports on the invoice.  Depending on the level of tax detail that you want, it may take a little effort.  I believe the standard invoice has the tax schedule keys, so you could use that to trigger a "flag" for taxed or not.  If you need the actual tax amount per line, you will need to link other tables and do a little work to get it set up.

Good Luck!

[oyama]

I looked into the tax schedule but it does not tell me if the line item was charged with tax or not. It tells me what the tax could be. There can be always a manual tax override.

I know it is not easy to calculate the tax for line items on the Crystal Reports but I do not have any clue how to even start it. 

[tbrasel]

If you don't have Crystal resources available, I would start by finding some.  I would assume your reseller could help.  Adding line item tax can be done, but needs to be done correctly as it involves adding additional tables to the invoice.  Any incorrect links, and you get a blank invoice printing.

Good Luck!

[rsisk101 · Accepted solution]

You'll need to join in tarInvoiceLineDist linking it tarPrintInvcDtlWrk on the InvoiceLineKey, then join in tciSTaxCodeTran linking it to tarInvoiceLineDist on the sTaxTranKey

Here is a modified version of the query generated by the Invoice report:

SELECT TaxSum.ActSTaxAmt, "tarPrintInvcHdrWrk"."TranAmt", ... , "tarPrintInvcHdrWrk"."HardCopy" 
  FROM  tarPrintInvcHdrWrk 
   INNER JOIN tarPrintInvcDtlWrk ON ("tarPrintInvcHdrWrk"."InvcKey"="tarPrintInvcDtlWrk"."InvcKey") 
          AND ("tarPrintInvcHdrWrk"."SessionID"="tarPrintInvcDtlWrk"."SessionID") 
   LEFT OUTER JOIN tciAddressWrk ON ("tarPrintInvcHdrWrk"."SessionID"="tciAddressWrk"."SessionID") 
          AND ("tarPrintInvcHdrWrk"."BillToAddrKey"="tciAddressWrk"."AddrKey") 
   LEFT OUTER JOIN tciAddressWrk tciAddressWrk_1 ON ("tarPrintInvcHdrWrk"."SessionID"="tciAddressWrk_1"."SessionID")
          AND ("tarPrintInvcHdrWrk"."ShipToAddrKey"="tciAddressWrk_1"."AddrKey") 
   LEFT OUTER JOIN tciAddressWrk tciAddressWrk_2 ON ("tarPrintInvcHdrWrk"."SessionID"="tciAddressWrk_2"."SessionID") 
          AND ("tarPrintInvcHdrWrk"."CompanyAddrKey"="tciAddressWrk_2"."AddrKey") 
   LEFT OUTER JOIN tarInvoiceLineDist ON tarPrintInvcDtlWrk.InvoiceLineKey = tarInvoiceLineDist.InvoiceLineKey
   JOIN (SELECT tciSTaxCodeTran.sTaxTranKey, SUM(ActNonRecoverAmt) ActNonRecoverAmt, SUM(ActSTaxAmt) ActSTaxAmt, 
                SUM(ActUseTaxAmt) ActUseTaxAmt, SUM(CalcNonRecoverAmt) CalcNonRecoverAmt, SUM(CalcSTaxAmt) CalcSTaxAmt, 
                SUM(CalcUseTaxAmt) CalcUseTaxAmt, SUM(ExmptAmt) ExmptAmt, SUM(SubjFreightAmt) SubjFreightAmt, 
                SUM(SubjSales) SubjSales, SUM(SubjSTaxAmt) SubjSTaxAmt
           FROM tciSTaxCodeTran tciSTaxCodeTran
          GROUP BY tciSTaxCodeTran.sTaxTranKey) TaxSum ON tarInvoiceLineDist.STaxTranKey = TaxSum.STaxTranKey 
WHERE "tarPrintInvcHdrWrk"."SessionID"=34 
  AND "tarPrintInvcHdrWrk"."HardCopy"=1 
ORDER BY tarPrintInvcHdrWrk."TranType" ASC,tarPrintInvcHdrWrk."CustID" ASC, 
         tarPrintInvcHdrWrk."TranNo" ASC , tarPrintInvcDtlWrk."InvoiceLineKey"ASC

I highlighted the new code to make it easier to identify.

This will sum the tax amount for each invoice line and works when the tax is manually overridden, or if there are multiple tax amounts, etc....

[oyama]

Awesome! This is exactly what I needed. It seems this solution is actually easier than I thought it would be.

Thank you!