Friday, April 12, 2013

E-business Tax: Query to list TAX_RATE_CODE based on Operating Unit in R12













In R12, we have E-Business Tax module to maintain the Tax Details. Every TAX_RATE_CODE is attached to a TAX_REGIME and in-turn a TAX_REGIME is tagged to one or more Operating Units. Below query is used to list down the TAX_RATE_CODES attached to a Operating Unit.

SELECT hou.organization_id,
       hou.set_of_books_id,
       hou.default_legal_context_id,
       hou.short_code,
       hou.NAME,
       zru.first_pty_org_id party_tax_profile_id,
       zru.tax_regime_id,
       zru.tax_regime_code,
       zxr.tax,
       zxr.tax_status_code,
       zxr.tax_rate_code,
       zxr.tax_jurisdiction_code,
       zxr.rate_type_code,
       zxr.recovery_type_code,
       zxr.percentage_rate,
       zxr.tax_rate_id,
       zxr.effective_from,
       zxr.effective_to,
       zxr.active_flag,
       zxr.attribute3,
       zxr.offset_tax,
       zxr.offset_status_code,
       zxr.offset_tax_rate_code
  FROM zx_party_tax_profile      ptp,
       zx_subscription_details   zsd,
       hr_operating_units        hou,    
       zx_regimes_usages         zru,
       zx_rates_vl               zxr
 WHERE zxr.tax_regime_code         = zru.tax_regime_code  
   AND ptp.party_type_code         = 'OU'  
   AND ptp.party_id                = hou.organization_id
   AND zru.first_pty_org_id        = ptp.party_tax_profile_id
   AND zru.first_pty_org_id        = zsd.first_pty_org_id
   AND zsd.tax_regime_code         = ZRU.TAX_REGIME_CODE
   AND zsd.parent_first_pty_org_id = -99
   AND SYSDATE BETWEEN zsd.effective_from AND NVL(zsd.effective_to,SYSDATE);
Below query is to list down the tax account based on Operating Unit,
SELECT hou.organization_id,
       hou.NAME,
       tax_account_ccid,
       zxr.tax,
       zxr.tax_status_code,
       zxr.tax_regime_code,
       zxr.tax_rate_code,
       zxr.tax_jurisdiction_code,
       zxr.rate_type_code,
       zxr.percentage_rate,
       zxr.tax_rate_id,
       zxr.effective_from,
       zxr.effective_to,
       zxr.active_flag
  FROM zx_rates_vl        zxr,
       zx_accounts        b,
       hr_operating_units hou
 WHERE b.internal_organization_id = hou.organization_id
   AND b.tax_account_entity_code = 'RATES'
   AND b.tax_account_entity_id = zxr.tax_rate_id
   AND zxr.active_flag = 'Y'
   AND SYSDATE BETWEEN zxr.effective_from AND nvl(zxr.effective_to, SYSDATE);

4 Responses to “E-business Tax: Query to list TAX_RATE_CODE based on Operating Unit in R12”

Raju said...
October 5, 2016 at 5:21 AM

Change the where condition of the first query as
AND SYSDATE BETWEEN zxr.effective_from AND NVL(zxr.effective_to,SYSDATE);


Anonymous said...
June 9, 2017 at 11:02 AM

Good work.. thank you


Mr.San said...
October 16, 2018 at 1:37 AM

Thanks for helpful query


samsung 9 kg fully automatic top load washing machine said...
June 19, 2021 at 5:22 AM

Enjoyed every single word of your blog article. Thanks to share it!


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.