Tax Reports Performance Issue: U.S. Sales Tax Report (ZXXSTR) Performance Problem (Doc ID 1362754.1)

Last updated on OCTOBER 19, 2016

Applies to:

Oracle E-Business Tax - Version 12.1.3 and later
Oracle Receivables - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Oracle Applications Tax Module

The U.S. Sales Tax Report (ZXXSTR) is having performance issues and does not finish.

Tkprof shows the following SQL statement as the one causing the problem:

SELECT /*+ leading ( dist trx zx_det su ) */ trx . invoice_currency_code
C_CURRENCY , cy . precision C_PRECISION , cy . minimum_accountable_unit
C_MINIMUM_ACCOUNTABLE_UNIT , upper ( loc . state ) C_STATE , decode ( :
p_detail_level , 'Detail State' , 'X' , loc . county ) C_COUNTY , decode (
...
...
...
C_VENDOR_TAXABLE_AMTS , NULL C_VENDOR_NON_TAXABLE_AMTS , NULL
C_VENDOR_EXEMPT_AMTS FROM zx_lines_det_factors trx , hz_cust_site_uses_all
su , hz_cust_acct_sites_all acct_site , hz_party_sites party_site ,
hz_locations loc , hz_cust_accounts cust_acct , hz_parties party ,
ar_adjustments_all adj WHERE trx.APPLICATION_ID = 222 AND
trx.TAX_REPORTING_FLAG = 'Y' AND nvl ( trx.SHIP_TO_CUST_ACCT_SITE_USE_ID ,
trx.BILL_TO_CUST_ACCT_SITE_USE_ID ) = su.site_use_id AND
su.cust_acct_site_id = acct_site.cust_acct_site_id AND
acct_site.party_site_id = party_site.party_site_id AND loc.location_id =
party_site.location_id AND loc.country = 'US' AND cust_acct.cust_account_id
= trx.BILL_THIRD_PTY_ACCT_ID AND party.party_id = cust_acct.party_id AND
adj.customer_trx_id = trx.trx_id and adj.type = 'TAX' AND not exists (
select 1 from zx_lines lines where lines.INTERNAL_ORGANIZATION_ID =
trx.INTERNAL_ORGANIZATION_ID AND trx.APPLICATION_ID = LINES.APPLICATION_ID
AND trx.ENTITY_CODE = lines.entity_code AND trx.EVENT_CLASS_CODE =
LINES.EVENT_CLASS_CODE AND trx.TRX_ID = LINES.TRX_ID AND trx.TRX_LINE_ID =
LINES.TRX_LINE_ID ) AND trx.trx_currency_code between DECODE ( :
p_currency_low , : p_all , trx.trx_currency_code , : p_currency_low ) AND
DECODE ( : p_currency_high , : p_all , trx.trx_currency_code , :
p_currency_high ) and adj.chargeback_customer_trx_id is null and
adj.approved_by is not null and 1 = 1 AND NVL (
trx.INTERNAL_ORGANIZATION_ID , : p_reporting_entity_id ) = :
p_reporting_entity_id AND 1 = 1 and 1 = 1 AND NVL ( adj.ORG_ID , :
p_reporting_entity_id ) = : p_reporting_entity_id AND adj.gl_date between
to_date ( '01-MAY-2011' , 'DD-MON-YYYY' ) AND to_date ( '28-MAY-2011' ,
'DD-MON-YYYY' ) and adj.apply_date between : p_adj_date_low and :
p_adj_date_high ORDER BY 1 ASC,4 ASC,5 ASC,6 ASC,8 ASC,9 ASC,10 ASC,11 ASC,
26 ASC,14 ASC,20 ASC,27 ASC,29 ASC,31 ASC,30 ASC,34 ASC,35 ASC,51 ASC,52
ASC,53 ASC,54 ASC,55 ASC,56 ASC,57 ASC,58 ASC,59 ASC,50 ASC,40 ASC,60 ASC,
61 ASC

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms