VIEW: APPS.IGF_AP_FA_CON_V

Source

View Text - Preformatted

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'

View Text - HTML Formatted

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'