Product: | CE - Cash Management |
---|---|
Description: | |
Implementation/DBA Data: | APPS.CE_INTEREST_RATES_V |
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