My Oracle Support Banner

Performance Issue On SHIPMENT INTERFACE IMPORT ZL.TAX TABLE (Doc ID 2650304.1)

Last updated on MARCH 19, 2020

Applies to:

Oracle Landed Cost Management - Version 12.1.3 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Landed Cost Calculation

ACTUAL BEHAVIOR
---------------
Performance issue on SHIPMENT INTERFACE IMPORT

We are seeing performance issue on SHIPMENT INTERFACE IMPORT program when passing GROUP ID as a parameter while running multiple programs in parallel. We are currently on 12.1.3. Our DBAs analyzed the long running queries and found below SQLs taking long time to complete

DELETE FROM INL_ASSOCIATIONS ASSOC WHERE ASSOC.FROM_PARENT_TABLE_NAME = 'INL_TAX_LINES' AND NOT EXISTS (SELECT 1 FROM INL_TAX_LINES TL WHERE ASSOC.FROM_PARENT_TABLE_ID = TL.TAX_LINE_ID)

SELECT SL_ZX.TAX, SL_ZX.TAX_LINE_ID, NVL(SL_ZX.TAX_AMT,0) TAX_AMT_TAX_CURR,
  NVL(SL_ZX.REC_TAX_AMT,0) REC_TAX_AMT_TAX_CURR, SL_ZX.TAX_CURRENCY_CODE,
  SL_ZX.CURRENCY_CONVERSION_RATE, SL_ZX.TAX_CURRENCY_CONVERSION_TYPE,
  SL_ZX.TAX_CURRENCY_CONVERSION_DATE, SL_ZX.TAX_AMT_INCLUDED_FLAG,
  NVL(SL_ZX.TRX_LINE_QUANTITY,0) TRX_LINE_QUANTITY, NVL(SL_ZX.LINE_AMT,0)
  LINE_AMT, SL_ZX.TRX_CURRENCY_CODE, SL_ZX.SHIP_LINE_ID,
  SL_ZX.PO_LINE_LOCATION_ID, SL_ZX.SL_CURRENCY_CODE,
  SL_ZX.SL_CURRENCY_CONVERSION_TYPE, SL_ZX.SL_CURRENCY_CONVERSION_DATE,
  NVL(SL_ZX.SL_TXN_QTY,0) SL_TXN_QTY, NVL(SL_ZX.SL_TXN_UNIT_PRICE,0)
  SL_TXN_UNIT_PRICE, TL_ASSOC.INL_TAX_LINE_ID, TL_ASSOC.INL_TAX_LINE_NUM
FROM
(SELECT ZL.TAX, ZL.TAX_LINE_ID, ZL.TAX_AMT, ZL.REC_TAX_AMT,
  ZL.TAX_CURRENCY_CODE, ZL.TAX_CURRENCY_CONVERSION_TYPE,
  ZL.TAX_CURRENCY_CONVERSION_DATE, ZL.TAX_AMT_INCLUDED_FLAG,
  ZL.TRX_LINE_QUANTITY, ZL.LINE_AMT, ZL.TRX_CURRENCY_CODE,
  DECODE(ZL.TAX_CURRENCY_CONVERSION_TYPE,NULL,NULL,
  ZL.CURRENCY_CONVERSION_RATE) CURRENCY_CONVERSION_RATE, SL.SHIP_HEADER_ID,
  SL.SHIP_LINE_SOURCE_ID PO_LINE_LOCATION_ID, SL.CURRENCY_CODE
  SL_CURRENCY_CODE, SL.CURRENCY_CONVERSION_TYPE SL_CURRENCY_CONVERSION_TYPE,
  SL.CURRENCY_CONVERSION_DATE SL_CURRENCY_CONVERSION_DATE, SL.TXN_QTY
  SL_TXN_QTY, SL.TXN_UNIT_PRICE SL_TXN_UNIT_PRICE, SL.SHIP_LINE_ID FROM
  INL_SHIP_LINES SL, ZX_LINES ZL WHERE SL.SHIP_HEADER_ID = :B1 AND
  SL.SHIP_LINE_SRC_TYPE_CODE = 'PO' AND SL.SHIP_LINE_SOURCE_ID =
  ZL.TRX_LINE_ID AND ZL.APPLICATION_ID = 201 AND ZL.EVENT_CLASS_CODE =
 
b

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
References


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