eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CE_INTEREST_RATES_V

Source

View Text - Preformatted


SELECT DISTINCT BR.INTEREST_SCHEDULE_ID , to_number(null) , to_number(null) , IR.EFFECTIVE_DATE , IR.EFFECTIVE_DATE , 'OLD' , trunc(sysdate) , nvl(fnd_global.user_id,-1) , trunc(sysdate) , nvl(fnd_global.user_id,-1) , nvl(fnd_global.user_id,-1) , IR.OBJECT_VERSION_NUMBER , (SELECT IR1.INTEREST_RATE FROM CE_INTEREST_RATES IR1 WHERE IR1.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR1.BALANCE_RANGE_ID = (SELECT min(BR1.BALANCE_RANGE_ID) FROM CE_INTEREST_BAL_RANGES BR1 WHERE BR1.INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 2 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 3 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 4 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 5 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 6 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 7 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 8 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 9 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 10 GROUP BY ROWNUM, BALANCE_RANGE_ID )) BAL_RANGE10 , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 11 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 12 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 13 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 14 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 15 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 16 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 17 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 18 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 19 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 20 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 21 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 22 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 23 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 24 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , (SELECT IR2.INTEREST_RATE FROM CE_INTEREST_RATES IR2 WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID FROM (SELECT BR2.BALANCE_RANGE_ID, BR2.INTEREST_SCHEDULE_ID FROM CE_INTEREST_BAL_RANGES BR2 WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID order by BR2.BALANCE_RANGE_ID) WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING rownum = 25 GROUP BY ROWNUM, BALANCE_RANGE_ID )) , to_number(null) FROM CE_INTEREST_RATES IR , CE_INTEREST_BAL_RANGES BR WHERE BR.BALANCE_RANGE_ID = IR.BALANCE_RANGE_ID GROUP BY IR.EFFECTIVE_DATE , BR.INTEREST_SCHEDULE_ID , IR.OBJECT_VERSION_NUMBER order by BR.INTEREST_SCHEDULE_ID , IR.EFFECTIVE_DATE

View Text - HTML Formatted

SELECT DISTINCT BR.INTEREST_SCHEDULE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, IR.EFFECTIVE_DATE
, IR.EFFECTIVE_DATE
, 'OLD'
, TRUNC(SYSDATE)
, NVL(FND_GLOBAL.USER_ID
, -1)
, TRUNC(SYSDATE)
, NVL(FND_GLOBAL.USER_ID
, -1)
, NVL(FND_GLOBAL.USER_ID
, -1)
, IR.OBJECT_VERSION_NUMBER
, (SELECT IR1.INTEREST_RATE
FROM CE_INTEREST_RATES IR1
WHERE IR1.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR1.BALANCE_RANGE_ID = (SELECT MIN(BR1.BALANCE_RANGE_ID)
FROM CE_INTEREST_BAL_RANGES BR1
WHERE BR1.INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 2 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 3 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 4 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 5 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 6 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 7 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 8 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 9 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 10 GROUP BY ROWNUM
, BALANCE_RANGE_ID )) BAL_RANGE10
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 11 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 12 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 13 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 14 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 15 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 16 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 17 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 18 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 19 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 20 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 21 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 22 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 23 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 24 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, (SELECT IR2.INTEREST_RATE
FROM CE_INTEREST_RATES IR2
WHERE IR2.EFFECTIVE_DATE = IR.EFFECTIVE_DATE
AND IR2.BALANCE_RANGE_ID = (SELECT BALANCE_RANGE_ID
FROM (SELECT BR2.BALANCE_RANGE_ID
, BR2.INTEREST_SCHEDULE_ID
FROM CE_INTEREST_BAL_RANGES BR2
WHERE BR2.INTEREST_SCHEDULE_ID = INTEREST_SCHEDULE_ID ORDER BY BR2.BALANCE_RANGE_ID)
WHERE INTEREST_SCHEDULE_ID = BR.INTEREST_SCHEDULE_ID HAVING ROWNUM = 25 GROUP BY ROWNUM
, BALANCE_RANGE_ID ))
, TO_NUMBER(NULL)
FROM CE_INTEREST_RATES IR
, CE_INTEREST_BAL_RANGES BR
WHERE BR.BALANCE_RANGE_ID = IR.BALANCE_RANGE_ID GROUP BY IR.EFFECTIVE_DATE
, BR.INTEREST_SCHEDULE_ID
, IR.OBJECT_VERSION_NUMBER ORDER BY BR.INTEREST_SCHEDULE_ID
, IR.EFFECTIVE_DATE