Loan Quality Control Queries

This article provides a list of SQL queries used to review and verify loan data in PeopleSoft.

Context

This article provides a list of SQL queries used to review and verify loan data in PeopleSoft.

Queries

1. UDSFS_EXP_GRAD_LOANS: Used to QC sub/unsub loan splits for students expected to graduate in the Fall. 

SQL

1. UDSFS_EXP_GRAD_LOANS: SELECT A.EMPLID,A.AID_YEAR,A.ITEM_TYPE,B.DESCR,A.OFFER_AMOUNT,A.DISBURSED_AMOUNT,A.DISBURSEMENT_PLAN,A.SPLIT_CODE FROM PS_STDNT_AWARDS A,PS_ITEM_TYPE_FA B WHERE(A.AID_YEAR=:2 AND A.OFFER_AMOUNT>0 AND A.AID_YEAR=B.AID_YEAR AND A.ITEM_TYPE=B.ITEM_TYPE AND B.SETID=A.SETID AND B.EFFDT=(SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_FA B_ED WHERE B.SETID=B_ED.SETID AND B.ITEM_TYPE=B_ED.ITEM_TYPE AND B.AID_YEAR=B_ED.AID_YEAR AND B_ED.EFFDT<=SYSDATE) AND B.LOAN_PROGRAM='D' AND B.FEDERAL_ID IN('STFS','STFU') AND A.EMPLID IN(SELECT C.EMPLID FROM PS_STDNT_FA_TERM C WHERE C.EFFDT=(SELECT MAX(C_ED.EFFDT) FROM PS_STDNT_FA_TERM C_ED WHERE C.EMPLID=C_ED.EMPLID AND C.INSTITUTION=C_ED.INSTITUTION AND C.STRM=C_ED.STRM AND C_ED.EFFDT<=SYSDATE) AND C.EFFSEQ=(SELECT MAX(C_ES.EFFSEQ) FROM PS_STDNT_FA_TERM C_ES WHERE C.EMPLID=C_ES.EMPLID AND C.INSTITUTION=C_ES.INSTITUTION AND C.STRM=C_ES.STRM AND C.EFFDT=C_ES.EFFDT) AND C.EXP_GRAD_TERM=:1 AND C.AID_YEAR=:2));