Winter Awarding Quality Control Queries

This article provides a list of SQL queries used to review winter awarding data in PeopleSoft.

Context

This article provides a list of SQL queries used to review winter awarding data in PeopleSoft.

Queries

1. UDSFS_WINTER_EXP_GRAD_QC: Used to identify students who are expected to graduate in the winter. Helps identify any DL/Pell that may need the dates updated. 

SQL

1. UDSFS_WINTER_EXP_GRAD_QC: SELECT A.EMPLID, A.ACAD_CAREER, A.EXP_GRAD_TERM, H.PKG_PLAN_ID, C.UNT_TAKEN_PRGRSS, SUM( D.OFFER_AMOUNT), SUM( F.OFFER_AMOUNT)
     FROM ((((PS_ACAD_PROG A LEFT OUTER JOIN  PS_STDNT_CAR_TERM C ON  A.EMPLID = C.EMPLID AND A.ACAD_CAREER = C.ACAD_CAREER AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND C.INSTITUTION = A.INSTITUTION AND C.STRM = :1 + 2 ) LEFT OUTER JOIN  PS_STDNT_AWARDS D ON  A.EMPLID = D.EMPLID AND A.ACAD_CAREER = D.ACAD_CAREER AND D.INSTITUTION = A.INSTITUTION AND D.AID_YEAR = :2 AND D.ITEM_TYPE IN (SELECT E.ITEM_TYPE
     FROM PS_ITEM_TYPE_FA E
     WHERE E.EFFDT =
           (SELECT MAX(E_ED.EFFDT) FROM PS_ITEM_TYPE_FA E_ED
           WHERE E.SETID = E_ED.SETID
             AND E.ITEM_TYPE = E_ED.ITEM_TYPE
             AND E.AID_YEAR = E_ED.AID_YEAR
             AND E_ED.EFFDT <= SYSDATE)
        AND E.FA_SOURCE = 'F'
        AND E.FEDERAL_ID IN ('STFU','STFS','PLUS')
        AND E.AID_YEAR = :2) ) LEFT OUTER JOIN  PS_STDNT_DISB_AWD F ON  A.EMPLID = F.EMPLID AND A.ACAD_CAREER = F.ACAD_CAREER AND F.INSTITUTION = A.INSTITUTION AND F.AID_YEAR = :2 AND F.ITEM_TYPE IN (SELECT G.ITEM_TYPE
     FROM PS_ITEM_TYPE_FA G
     WHERE G.EFFDT =
           (SELECT MAX(G_ED.EFFDT) FROM PS_ITEM_TYPE_FA G_ED
           WHERE G.SETID = G_ED.SETID
             AND G.ITEM_TYPE = G_ED.ITEM_TYPE
             AND G.AID_YEAR = G_ED.AID_YEAR
             AND G_ED.EFFDT <= SYSDATE)
        AND G.AID_YEAR = :2
        AND G.FEDERAL_ID = 'PELL') ) LEFT OUTER JOIN  PS_STDNT_AID_ATRBT H ON  A.EMPLID = H.EMPLID AND A.ACAD_CAREER = H.ACAD_CAREER AND H.INSTITUTION = A.INSTITUTION AND H.AID_YEAR = :2 )
     WHERE ( A.EFFDT =
           (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
           WHERE A.EMPLID = A_ED.EMPLID
             AND A.ACAD_CAREER = A_ED.ACAD_CAREER
             AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
             AND A_ED.EFFDT <= SYSDATE)
       AND A.EFFSEQ =
           (SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
           WHERE A.EMPLID = A_ES.EMPLID
             AND A.ACAD_CAREER = A_ES.ACAD_CAREER
             AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
             AND A.EFFDT = A_ES.EFFDT)
        AND A.EXP_GRAD_TERM = :1
        AND A.EMPLID IN (SELECT B.EMPLID
     FROM PS_ADM_APPL_PROG B, PS_ADM_MAINT_SCTY B1
     WHERE B.EMPLID = B1.EMPLID
       AND B.ACAD_CAREER = B1.ACAD_CAREER
       AND B.STDNT_CAR_NBR = B1.STDNT_CAR_NBR
       AND B.ADM_APPL_NBR = B1.ADM_APPL_NBR
       AND B.APPL_PROG_NBR = B1.APPL_PROG_NBR
       AND B1.OPRCLASS = 'HCPPALL'
       AND B1.OPRID = '702050948'
       AND ( B.EFFDT =
           (SELECT MAX(B_ED.EFFDT) FROM PS_ADM_APPL_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.ADM_APPL_NBR = B_ED.ADM_APPL_NBR
             AND B.APPL_PROG_NBR = B_ED.APPL_PROG_NBR
             AND B_ED.EFFDT <= SYSDATE)
       AND B.EFFSEQ =
           (SELECT MAX(B_ES.EFFSEQ) FROM PS_ADM_APPL_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.ADM_APPL_NBR = B_ES.ADM_APPL_NBR
             AND B.APPL_PROG_NBR = B_ES.APPL_PROG_NBR
             AND B.EFFDT = B_ES.EFFDT)
        AND B.PROG_STATUS IN ('AC','AD','PM') ))
        AND A.INSTITUTION = 'UOD01')
     GROUP BY  A.EMPLID,  A.ACAD_CAREER,  A.EXP_GRAD_TERM,  H.PKG_PLAN_ID,  C.UNT_TAKEN_PRGRSS
     ORDER BY 1