create view XXXX
as
SELECT a.*, SUM(R_TOTAL) OVER (PARTITION BY P_NO ORDER BY MNTH) PROG_TOTAL,
p_no, mnth
FROM
(
SELECT S.P_NO, S.MNTH, nvl(S.SUBS_AMT, 0)subs_amt,
nvl(S.REFUND_DRAW_AMT, 0)refund_amt,
nvl(T.TR_AMT, 0)tr_amt, nvl(A.AMOUNT, 0)amount,
(case when TO_CHAR(S.MNTH, ''MON'') = ''JUN'' THEN
(NVL(OPENING_BAL, 0) + nvl(S.SUBS_AMT, 0) + nvl(S.REFUND_DRAW_AMT, 0) +
nvl(T.TR_AMT,0)) - nvl(A.AMOUNT, 0)
ELSE
nvl(S.SUBS_AMT, 0) + nvl(S.REFUND_DRAW_AMT, 0) + nvl(T.TR_AMT,0) -
nvl(A.AMOUNT, 0)END)r_total
FROM GPF_SUBS S, GPF_TRS T, GPF_ADVANCES A, GPF_STATEMENTS M
WHERE S.P_NO = T.P_NO(+)
AND S.P_NO = A.P_NO(+)
AND S.P_NO = M.P_NO(+)
AND S.MNTH = T.MNTH(+)
AND S.MNTH = A.MNTH(+)
AND FY_GPF_TOTAL.GET_FY(S.MNTH) = FY_GPF_TOTAL.GET_FY(M.YEARS)
)a
and query the view
since your predicate is:
where p_no = :P_P_NO
and FY_GPF_TOTAL.GET_FY(a.MNTH) = :P_FIN_YEAR' using p_p_no, p_fin_year;
you would need to expose those two columns in the view as well.
since you partition by p_no - that part of the predicate can be pushed into the view nicely.