Body
Screenshot:
Query:
UDSFS_POPUP_BUDGET_RECALC_QRY
Query Details:
SELECT DISTINCT A.EMPLID, A.INSTITUTION, A.STRM, TO_CHAR(A.EFFDT,'YYYY-MM-DD'), A.EFFSEQ
FROM PS_SCCPU_FATRM_BND A
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_SCCPU_FATRM_BND A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.INSTITUTION = A_ED.INSTITUTION
AND A.STRM = A_ED.STRM
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_SCCPU_FATRM_BND A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.INSTITUTION = A_ES.INSTITUTION
AND A.STRM = A_ES.STRM
AND A.EFFDT = A_ES.EFFDT)
AND A.STRM = :1
AND A.INSTITUTION = 'UOD01'
AND A.EMPLID IN (SELECT B.EMPLID
FROM PS_ACAD_PROG B
WHERE B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PROG B_ED
WHERE B.EMPLID = B_ED.EMPLID
AND B.ACAD_CAREER = B_ED.ACAD_CAREER
AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR
AND B_ED.EFFDT <= SYSDATE)
AND B.EFFSEQ =
(SELECT MAX(B_ES.EFFSEQ) FROM PS_ACAD_PROG B_ES
WHERE B.EMPLID = B_ES.EMPLID
AND B.ACAD_CAREER = B_ES.ACAD_CAREER
AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR
AND B.EFFDT = B_ES.EFFDT)
AND B.ACAD_CAREER = :2
AND B.PROG_STATUS IN ('AC','AD','PM')
AND B.INSTITUTION = 'UOD01')
AND A.EMPLID IN (SELECT C.EMPLID
FROM PS_STDNT_BUDGET_IT C
WHERE C.EFFDT =
(SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_BUDGET_IT C_ED
WHERE C.EMPLID = C_ED.EMPLID
AND C.INSTITUTION = C_ED.INSTITUTION
AND C.AID_YEAR = C_ED.AID_YEAR
AND C.ACAD_CAREER = C_ED.ACAD_CAREER
AND C.STRM = C_ED.STRM
AND C_ED.EFFDT <= SYSDATE)
AND C.EFFSEQ =
(SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_BUDGET_IT C_ES
WHERE C.EMPLID = C_ES.EMPLID
AND C.INSTITUTION = C_ES.INSTITUTION
AND C.AID_YEAR = C_ES.AID_YEAR
AND C.ACAD_CAREER = C_ES.ACAD_CAREER
AND C.STRM = C_ES.STRM
AND C.EFFDT = C_ES.EFFDT)
AND C.INSTITUTION = 'UOD01'
AND C.ACAD_CAREER = :2
AND C.STRM = :1
AND C.BUDGET_ITEM_AMOUNT > 0))