View: IGF_AP_FA_CON_V

View Text

SELECT FA.ROWID ROW_ID
, FA.BASE_ID
, FA.PERSON_ID
, FA.CI_CAL_TYPE
, FA.CI_SEQUENCE_NUMBER
, PIT.API_PERSON_ID SSN
, PE.PERSON_LAST_NAME
, PE.PERSON_FIRST_NAME
, PE.PERSON_LAST_NAME||'
, '||PE.PERSON_FIRST_NAME FULL_NAME
, CA.ALTERNATE_CODE CI_ALTERNATE_CODE
, CA.START_DT START_DT
, CA.END_DT END_DT
, PE.PARTY_NUMBER PERSON_NUMBER
, FA.ORG_ID
, NULL
, NULL
, NULL
, NULL
, VERIFICATION_PROCESS_RUN
, COA_PENDING
, INST_VERIF_STATUS_DATE
, MANUAL_VERIF_FLAG
, NSLDS_ELIGIBLE
, FED_VERIF_STATUS_DATE
, FED_VERIF_STATUS
, INST_VERIF_STATUS
, ISIR_CORR_STATUS_DATE
, EDE_CORRECTION_BATCH_ID
, ISIR_CORR_STATUS
, ISIR_STATUS_DATE
, PROFILE_STATUS
, PROFILE_STATUS_DATE
, PROFILE_FC
, FA_PROCESS_STATUS_DATE
, ISIR_STATUS
, DISBURSEMENT_HOLD
, COA_CODE_F
, COA_CODE_I
, NOTIFICATION_STATUS
, PACKAGING_STATUS
, PACKAGING_STATUS_DATE
, PACKAGING_HOLD
, COA_F
, FA_PROCESS_STATUS
, TOTAL_PACKAGE_OFFERED
, TOTAL_PACKAGE_ACCEPTED
, NOTIFICATION_STATUS_DATE
, COA_I
, NULL
, RPAD(PI.ORIGINAL_SSN
, 9)||RPAD(PI.ORIG_NAME_ID
, 2)||PI.TRANSACTION_NUM ACTIVE_ISIR
, PI.DEPENDENCY_STATUS
, PROF_JUDGEMENT_FLG
, NSLDS_DATA_OVERRIDE_FLG
, PI.NSLDS_MATCH_FLAG
, COA_FIXED
, COA_PELL
, TARGET_GROUP
, SUBSTR(IGS_EN_GEN_003.ENRP_GET_ENCMBRD_IND (PE.PARTY_ID)
, 1
, 1) ENCUMBERED_IND
, HZP.DECEASED_IND
, ADMSTRUCT_ID
, ADMSEGMENT_1
, ADMSEGMENT_2
, ADMSEGMENT_3
, ADMSEGMENT_4
, ADMSEGMENT_5
, ADMSEGMENT_6
, ADMSEGMENT_7
, ADMSEGMENT_8
, ADMSEGMENT_9
, ADMSEGMENT_10
, ADMSEGMENT_11
, ADMSEGMENT_12
, ADMSEGMENT_13
, ADMSEGMENT_14
, ADMSEGMENT_15
, ADMSEGMENT_16
, ADMSEGMENT_17
, ADMSEGMENT_18
, ADMSEGMENT_19
, ADMSEGMENT_20
, PACKSTRUCT_ID
, PACKSEGMENT_1
, PACKSEGMENT_2
, PACKSEGMENT_3
, PACKSEGMENT_4
, PACKSEGMENT_5
, PACKSEGMENT_6
, PACKSEGMENT_7
, PACKSEGMENT_8
, PACKSEGMENT_9
, PACKSEGMENT_10
, PACKSEGMENT_11
, PACKSEGMENT_12
, PACKSEGMENT_13
, PACKSEGMENT_14
, PACKSEGMENT_15
, PACKSEGMENT_16
, PACKSEGMENT_17
, PACKSEGMENT_18
, PACKSEGMENT_19
, PACKSEGMENT_20
, MISCSTRUCT_ID
, MISCSEGMENT_1
, MISCSEGMENT_2
, MISCSEGMENT_3
, MISCSEGMENT_4
, MISCSEGMENT_5
, MISCSEGMENT_6
, MISCSEGMENT_7
, MISCSEGMENT_8
, MISCSEGMENT_9
, MISCSEGMENT_10
, MISCSEGMENT_11
, MISCSEGMENT_12
, MISCSEGMENT_13
, MISCSEGMENT_14
, MISCSEGMENT_15
, MISCSEGMENT_16
, MISCSEGMENT_17
, MISCSEGMENT_18
, MISCSEGMENT_19
, MISCSEGMENT_20
, DECODE(FA.AWARD_FMLY_CONTRIBUTION_TYPE
, '2'
, AI.SECONDARY_EFC
, AI.PRIMARY_EFC) PYMT_ISIR_EFC
, DECODE(PI.COMMENT_CODES
, NULL
, 'N'
, 'Y') COMMENT_CODES
, PI.APP_RECEIPT_DATE FAFSA_RCPT_DT
, FA.TOLERANCE_AMOUNT
, FA.CREATED_BY
, FA.CREATION_DATE
, FA.LAST_UPDATED_BY
, FA.LAST_UPDATE_DATE
, FA.LAST_UPDATE_LOGIN
, FA.REQUEST_ID
, FA.PROGRAM_APPLICATION_ID
, FA.PROGRAM_ID
, FA.PROGRAM_UPDATE_DATE
, FA.MANUAL_DISB_HOLD
, CA.DESCRIPTION CAL_DESCRIPTION
, FA.ASSOC_ORG_NUM
, FA.AWARD_FMLY_CONTRIBUTION_TYPE
, FA.ISIR_LOCKED_BY
, PI.MESSAGE_CLASS_TXT
, TLKP.MEANING ISIR_TYPE_DESC
, TLKP.LOOKUP_CODE ISIR_TYPE
, RPAD(AI.ORIGINAL_SSN
, 9)||RPAD(AI.ORIG_NAME_ID
, 2)||AI.TRANSACTION_NUM AWARDING_ISIR
, FA.ADNL_UNSUB_LOAN_ELIG_FLAG
, FA.LOCK_AWD_FLAG
, FA.LOCK_COA_FLAG
FROM IGF_AP_FA_BASE_REC_ALL FA
, IGF_AP_ISIR_MATCHED_ALL PI
, IGF_AP_ISIR_MATCHED_ALL AI
, HZ_PARTIES PE
, IGS_CA_INST_ALL CA
, (SELECT API_PERSON_ID
, PE_PERSON_ID
FROM IGS_PE_PERSON_ID_TYP PIT_2
, IGS_PE_ALT_PERS_ID API_2
WHERE API_2.PERSON_ID_TYPE = PIT_2.PERSON_ID_TYPE
AND PIT_2.S_PERSON_ID_TYPE = 'SSN'
AND SYSDATE BETWEEN API_2.START_DT
AND NVL(API_2.END_DT
, SYSDATE) ) PIT
, IGS_PE_HZ_PARTIES HZP
, IGF_LOOKUPS_VIEW MLKP
, IGF_LOOKUPS_VIEW TLKP
WHERE NVL(FA.ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)
AND FA.PERSON_ID = PE.PARTY_ID
AND FA.CI_CAL_TYPE = CA.CAL_TYPE
AND FA.CI_SEQUENCE_NUMBER = CA.SEQUENCE_NUMBER
AND FA.BASE_ID = PI.BASE_ID(+)
AND PI.PAYMENT_ISIR(+) = 'Y'
AND PI.SYSTEM_RECORD_TYPE(+) NOT IN ('INTERNAL'
, 'SIMULATION')
AND FA.BASE_ID = AI.BASE_ID(+)
AND AI.ACTIVE_ISIR(+) = 'Y'
AND AI.SYSTEM_RECORD_TYPE(+) NOT IN ('INTERNAL'
, 'SIMULATION')
AND PE.PARTY_ID =PIT.PE_PERSON_ID(+)
AND HZP.PARTY_ID = PE.PARTY_ID
AND PI.MESSAGE_CLASS_TXT = MLKP.LOOKUP_CODE(+)
AND MLKP.TAG = TLKP.LOOKUP_CODE(+)
AND MLKP.LOOKUP_TYPE(+) = 'IGF_AP_ISIR_MESSAGE_CLASS'
AND MLKP.ENABLED_FLAG(+) = 'Y'
AND TLKP.LOOKUP_TYPE(+) = 'IGF_AP_ISIR_TYPE'
AND TLKP.ENABLED_FLAG(+) = 'Y'

Columns