My Oracle Support Banner

Poor Performance With Tax Procedure During Bill Now (Doc ID 1922797.1)

Last updated on JULY 23, 2018

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.4.0.1.0 to 7.5.0.0.0 [Release 7.4.0 to 7.5.0]
Information in this document applies to any platform.

Goal

On BRM 7.4, there was very poor performance in production environment with the execution of the tax procedure during bill now.

From AWR report, this is the query causing performance:

"insert into event_billing_tax_cycles_t ( start_t, end_t, currency, bill_vat, bill_obj_DB, bill_obj_ID0, bill_obj_TYPE, bill_obj_REV, rec_id, obj_id0 ) values ( :start_t, :end_t, :currency, :bill_vat, :bill_obj_DB, :bill_obj_ID0, :bill_obj_TYPE, :bill_obj_REV, :rec_id, :obj_id0 )"

And, it triggered the performance problem in:

"SELECT TAX_CODE , SUM (AMOUNT_DEFERRED) "AMOUNT" , TAX_LOCALES FROM (SELECT /*+ INDEX(eb I_EVENT_BI_ITEM_OBJ__ID) ORDERED */ EB.RESOURCE_ID , EB.TAX_CODE , EB.AMOUNT_DEFERRED , E.TAX_LOCALES , E.TAX_SUPPLIER FROM ITEM_T I , EVENT_BAL_IMPACTS_T EB , BILL_T B , EVENT_T E WHERE EB.AMOUNT_DEFERRED != 0 AND EB.RESOURCE_ID = :b1 AND EB.OBJ_ID0 = E.POID_ID0 AND EB.ITEM_OBJ_ID0 = I.POID_ID0 AND I.POID_DB IS NOT NULL AND ( ( I.BILL_OBJ_ID0 = B.POID_ID0 AND I.OPENED_T >= 0 ) OR ( ( E.POID_TYPE LIKE '/event/billing/adjustment/%' OR E.POID_TYPE LIKE '/event/billing/dispute/%' OR E.POID_TYPE LIKE '/event/billing/settlement/%' ) AND ( :b2 = :b3 OR :b2 = I.BILLINFO_OBJ_ID0 ) AND :b4 < I.EFFECTIVE_T AND I.EFFECTIVE_T <= :b5 AND I.ARCHIVE_STATUS = 0 ) ) AND B.AR_BILLINFO_OBJ_ID0 = I.AR_BILLINFO_OBJ_ID0 AND B.POID_ID0 = :b6 AND I.AR_BILLINFO_OBJ_ID0 = :b3 AND E.TAX_SUPPLIER = :b7 AND EB.OBJ_ID0 = ( CASE WHEN BITAND(EB.OBJ_ID0 , 1152921504606846976 ) != 0 THEN ( CASE WHEN NOT EXISTS (SELECT START_POID_ID0 FROM TMP_UNPROCESSED_EVENTS_T WHERE EB.OBJ_ID0 BETWEEN START_POID_ID0 AND END_POID_ID0 ) THEN EB.OBJ_ID0 ELSE 0 END) ELSE EB.OBJ_ID0 END) AND ( ( I.BILL_OBJ_ID0 = :b6 ) OR ( I.AR_BILL_OBJ_ID0 = 0 AND ( I.EFFECTIVE_T = 0 OR I.EFFECTIVE_T <= :b5 ) ) ) ) GROUP BY TAX_CODE , TAX_LOCALES "


Note that in /config/tax_supplier, OOB configuration "pin_tax_supplier.xml" is configured with only one tax supplier:

<TaxSupplierConfiguration xmlns="http://www.portal.com/PortalXMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.portal.com/PortalXMLSchema ../../../xsd/pin_tax_supplier.xsd">
    <TaxSupplierElement>
       <Name>TS1</Name>
       <Description>Tax Supplier 1</Description>
       <Address>Cupertino;CA;95014;US</Address>
       <NexusInfo>*</NexusInfo>
       <RegulatedFlag>0</RegulatedFlag>
       <DefaultFlag>1</DefaultFlag>
       <VATInfo>
         <CanonCountry>US</CanonCountry>
         <VATCertificate>vat_cert_US</VATCertificate>
      </VATInfo>
   </TaxSupplierElement>
</TaxSupplierConfiguration>


Also, the tax is calculated for each charge when its generated.

What is the root cause of this performance issue and how to fix?

 

Solution

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
Goal
Solution

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