Missing AR Sales Tax Code

We had a problem in the AR Sales Tax Report which bugged us for several months.  We did our vertex upload every month into our Oracle Applications, however it seems that everytime we did the upload, some sales tax which is related to invoice is missing.

For example :

Trx 00001 has sales_tax_id = 121111 and it is existing in AR_SALES_TAX with sales tax rate of 4.5 %. However after vertex upload, sales_tax_id 121111 in AR_SALES_TAX is gone and replaced by a new sales_tax_id with the same rate ! This caused the AR US Sales Tax Report will not correctly reported the tax in right STATE – COUNTY – CITY format, instead it will put it in OTHERS field. The occurence were very sporadic and it was hard for us to find a pattern why.  The person who used to upload vertex also left the company and it made more difficult to find the reason why. We have raised TAR to oracle since FEB-07 but there was no meaningful answer from them (as usual).

Yesterday the user again complained of the missing sales tax and i managed to find out why. It seems someone have manually added a postal code to the AR location Rates , and it caused the whole sales_tax_id for the particular location_id gone and replaced by the new sales_tax_id. Since the issue was recent, I managed to find out that the user is using ABC Operating Unit instead of XYZ Operating Unit to add the postal code. Both OU are in the US so initially i thought this wouldn’t make a difference.

The problem actually lies with ARP_STAX.POPULATE_SALES_TAX called by the trigger in AR_LOCATION_RATES. There’s a checking within the package which goes like this :

If <sales tax id exists in RA_CUSTOMER_TRX_LINES> then

     disable the old sales_tax_id, create new sales_tax_id

else

    delete the old sales_tax_id, and create new sales_tax_id

end if;

Since the user added the postal code in B OU responsibility, then of course ARP_STAX package would not find any invoices related with the sales_tax_id in B OU because the invoice is in A OU. It is believed that the user also did the vertex upload using different OU rather than A OU, hence causing the problem.

So we solved the issue by advising them not to upload or modify the Tax Location Rates with other responsibility than A OU (which is used by US Sales Tax Report).