Thnx Slimdave. I got my answer by adding another table...

One more help. Is there a substitute for anlytical function LAG(). I mean can we write this is a different way.

'coz I cannot use LAG() function in FAST REFRESH MATERIALIZED VIEWS.

Issue boy...

LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
A.ACCOUNT_ID ORDER BY
TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID

I need you input on this. This is the script,

CREATE MATERIALIZED VIEW MV_SUB_DELINQUENCY_FLOW_CYCLE
REFRESH FAST ON DEMAND
AS
SELECT
B.PORTFOLIO_GROUP_ID,
A.SITE_ID,
A.ACCOUNT_ID,
A.CYCLE_NUMBER,
TRIM(C.CAL_MONTH_STR) REPORT_PERIOD,
LAG(A.DELINQUENCY_STATUS_ID) OVER (PARTITION BY
A.ACCOUNT_ID ORDER BY
TRIM(C.CAL_MONTH_STR) ASC) AS PRIOR_DELINQUENCY_STATUS_ID,
A.DELINQUENCY_STATUS_ID,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 AND A.BANKRUPTCY_CHGOFF > 0 THEN
'Y'
ELSE 'N' END) BANKRUPTCY_FLAG,
NVL(A.CYCLE_CLOSING_BAL, 0) CLOSING_BAL,
A.ROWID AS AROWID,
B.ROWID AS BROWID,
C.ROWID AS CROWID
FROM CYCLE_SUMMARY_FACT A,
PORTFOLIOS_DIM B,
CCRT_CALENDAR_DIM C
WHERE A.PORTFOLIO_ID = B.PORTFOLIO_ID
AND A.CYCLE_END_DATE = C.CAL_DAY_DT
AND A.ACCOUNT_STATUS_ID NOT IN (1, 2);