My Oracle Support Banner

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

Last updated on FEBRUARY 09, 2018

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

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
 1. Bug Summary
 2. Fixed Files
 3. Recommended Patches
 4. Solution Steps
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.