ISIR_COR_REJ_VW

(SQL View)
 Index Back

Pending Rej ISIR Corrections

This view contains all those rejected ISIR corrections which have be resubmitted.

SELECT A.AID_YEAR, A.EMPLID, A.INSTITUTION, A.ISIR_TXN_NBR, B.OLD_VALUE, B.NEW_VALUE, B.ISIR_FIELD_NUM, B.BATCH_ACTION, A.CORR_STATUS, A.BATCH_NUMBER FROM PS_ISIR_CORRECT A, PS_ISIR_CORR_FLDS B WHERE A.ASOFDATE = (SELECT MAX(A1.ASOFDATE) FROM PS_ISIR_CORRECT A1 WHERE A1.EMPLID = A.EMPLID AND A1.INSTITUTION = A.INSTITUTION AND A1.AID_YEAR = A.AID_YEAR AND A1.BATCH_NUMBER = A.BATCH_NUMBER AND A1.ASOFDATE <= %CURRENTDATEIN) AND A.SEQUENCE_NUMBER = (SELECT MAX(A2.SEQUENCE_NUMBER) FROM PS_ISIR_CORRECT A2 WHERE A2.EMPLID = A.EMPLID AND A2.INSTITUTION = A.INSTITUTION AND A2.AID_YEAR = A.AID_YEAR AND A2.BATCH_NUMBER = A.BATCH_NUMBER AND A2.ASOFDATE = A.ASOFDATE) AND A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.AID_YEAR = B.AID_YEAR AND A.BATCH_NUMBER = B.BATCH_NUMBER AND A.CORR_STATUS = 'R' AND B.BATCH_ACTION = 'R'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 AID_YEAR Character(4) VARCHAR2(4) NOT NULL Aid Year
2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
3 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Prompt Table: INSTITUTION_TBL

4 ISIR_TXN_NBR Number(2,0) SMALLINT NOT NULL Transaction Num
5 OLD_VALUE Character(50) VARCHAR2(50) NOT NULL Previous Field Value
6 NEW_VALUE Character(50) VARCHAR2(50) NOT NULL Submitted Value
7 ISIR_FIELD_NUM Character(4) VARCHAR2(4) NOT NULL ISIR Field Num
8 BATCH_ACTION Character(1) VARCHAR2(1) NOT NULL Batch Action Code
D=Delete
P=Pending
R=Resubmit
X=Processed
9 CORR_STATUS Character(1) VARCHAR2(1) NOT NULL Batch Status
P=Processed
R=Rejected
S=Submitted
X=Reprocessed
10 BATCH_NUMBER Character(23) VARCHAR2(23) NOT NULL CPS Batch Number