Fall Awarding Quality Control Queries

Context

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

Queries

  1. UDSFS_SARC_WITH_AWARDS: Used to identify students who have open FSARC checklists and offered awards. Helps identify admitted students that had overrides placed to give an initial award package.

SQL

  1. SELECT A.EMPLID, A.AID_YEAR, A.ACAD_CAREER, A.ITEM_TYPE, B.DESCR, A.DISBURSEMENT_PLAN, A.SPLIT_CODE, A.OFFER_AMOUNT
      FROM PS_STDNT_AWARDS A, PS_ITEM_TYPE_FA B
      WHERE ( A.AID_YEAR = :1
         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 A.EMPLID IN (SELECT C.COMMON_ID
      FROM PS_VAR_DATA_FINA C, PS_PERSON_CHECKLST D, PS_PERSON_CHK_ITEM E
      WHERE C.AID_YEAR = :1
         AND C.COMMON_ID = D.COMMON_ID
         AND C.VAR_DATA_SEQ = D.VAR_DATA_SEQ
         AND D.COMMON_ID = E.COMMON_ID
         AND D.SEQ_3C = E.SEQ_3C
         AND E.CHKLST_ITEM_CD = 'FSARC'
         AND E.ITEM_STATUS = 'I'))
Print Article