Oracle Apps: "Program - Revalue Balances" performance improvement suggestion
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') ;