SQL Performance (MOSC)

MOSC Banner

Oracle Apps: "Program - Revalue Balances" performance improvement suggestion

edited Jun 17, 2021 11:43AM in SQL Performance (MOSC) Question

We hit the sql by multiple sessions and after research, hit this doc from Oracle

GLCRVL R12:GL "Program - Revalue Balances" Performance Issue (Doc ID 2540170.1)

I went through the solution proposed by and have better solution...Which I tested also a bit...

Change the sql to ...

SELECT 

  CUR.currency_code 

FROM 

  FND_CURRENCIES CUR 

WHERE

  CUR.enabled_flag = 'Y' AND 

  CUR.currency_code NOT IN ('STAT', 'INR') and

  EXISTS ( 

  select 1 from GL_JE_HEADERS JEH

  where JEH.ledger_id = 362 AND 

  JEH.actual_flag = 'A' AND 

  JEH.status||'' = 'P' AND 

  JEH.currency_code = CUR.currency_code ) 

ORDER BY 

  NLSSORT(CUR.currency_code, 'NLS_SORT=BINARY') ;

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

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

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center