Product: | IGF - Financial Aid |
---|---|
Description: | |
Implementation/DBA Data: | APPS.IGF_SL_LOR_V |
SELECT LOR.ROW_ID
, LOR.ORIGINATION_ID
, LAR.LOAN_ID
, AWDV.AWARD_ID
, BASE.PERSON_ID
, AWDV.FUND_ID
, LAR.SEQ_NUM
, FMAST.CI_CAL_TYPE
, FMAST.CI_SEQUENCE_NUMBER
, CI.START_DT
, CI.END_DT
, CI.ALTERNATE_CODE
, FMAST.FUND_CODE
, FMAST.FUND_CODE||TO_CHAR(LAR.SEQ_NUM)
, FCAT.FUND_SOURCE
, FCAT.FUND_TYPE
, FCAT.SYS_FUND_TYPE
, FCAT.FED_FUND_CODE
, LAR.LOAN_NUMBER
, LOR.SCH_CERT_DATE
, LOR.ORIG_STATUS_FLAG
, LOR.ORIG_BATCH_ID
, LOR.ORIG_BATCH_DATE
, NULL
, LOR.ORIG_ACK_DATE
, LOR.CREDIT_OVERRIDE
, LOR.CREDIT_DECISION_DATE
, LOR.REQ_SERIAL_LOAN_CODE
, LOR.ACT_SERIAL_LOAN_CODE
, LOR.PNOTE_DELIVERY_CODE
, LOR.PNOTE_STATUS
, IGF_AW_GEN.LOOKUP_DESC('IGF_SL_PNOTE_STATUS'
, LOR.PNOTE_STATUS)
, LOR.PNOTE_STATUS_DATE
, LOR.PNOTE_ID
, LOR.PNOTE_PRINT_IND
, LOR.PNOTE_ACCEPT_AMT
, LOR.PNOTE_ACCEPT_DATE
, LOR.PNOTE_BATCH_ID
, LOR.PNOTE_ACK_DATE
, LOR.PNOTE_MPN_IND
, LOR.UNSUB_ELIG_FOR_HEAL
, LOR.DISCLOSURE_PRINT_IND
, LOR.ORIG_FEE_PERCT
, LOR.BORW_CONFIRM_IND
, LOR.BORW_INTEREST_IND
, LOR.BORW_OUTSTD_LOAN_CODE
, LOR.UNSUB_ELIG_FOR_DEPNT
, LOR.GUARANTEE_AMT
, LOR.GUARANTEE_DATE
, LOR.GUARNT_AMT_REDN_CODE
, LOR.GUARNT_STATUS_CODE
, LOR.GUARNT_STATUS_DATE
, LOR.LEND_APPRV_DENIED_CODE
, LOR.LEND_APPRV_DENIED_DATE
, LOR.LEND_STATUS_CODE
, LOR.LEND_STATUS_DATE
, LOR.GUARNT_ADJ_IND
, LOR.GRADE_LEVEL_CODE
, LOR.ENROLLMENT_CODE
, LOR.ANTICIP_COMPL_DATE
, RECIP.LENDER_ID
, NULL
, RECIP.GUARANTOR_ID
, NULL
, LOR.PRC_TYPE_CODE
, LOR.CL_SEQ_NUMBER
, LOR.LAST_RESORT_LENDER
, RECIP.LENDER_ID
, NULL
, RECIP.LEND_NON_ED_BRC_ID
, RECIP.RECIPIENT_ID
, RECIP.RECIPIENT_TYPE
, NULL
, RECIP.RECIP_NON_ED_BRC_ID
, LOR.REC_TYPE_IND
, LOR.CL_LOAN_TYPE
, LOR.CL_REC_STATUS
, LOR.CL_REC_STATUS_LAST_UPDATE
, LOR.ALT_PROG_TYPE_CODE
, LOR.ALT_APPL_VER_CODE
, LOR.MPN_CONFIRM_CODE
, LOR.RESP_TO_ORIG_CODE
, LOR.APPL_LOAN_PHASE_CODE
, LOR.APPL_LOAN_PHASE_CODE_CHG
, NULL
, LOR.TOT_OUTSTD_STAFFORD
, LOR.TOT_OUTSTD_PLUS
, LOR.ALT_BORW_TOT_DEBT
, LOR.ACT_INTEREST_RATE
, LOR.SERVICE_TYPE_CODE
, LOR.REV_NOTICE_OF_GUARNT
, LOR.SCH_REFUND_AMT
, LOR.SCH_REFUND_DATE
, LOR.UNIQ_LAYOUT_VEND_CODE
, LOR.UNIQ_LAYOUT_IDENT_CODE
, LOR.P_PERSON_ID
, NULL
, NULL
, LOR.P_PERMT_ADDR_CHG_DATE
, LOR.P_DEFAULT_STATUS
, LOR.P_SIGNATURE_CODE
, LOR.P_SIGNATURE_DATE
, NULL
, NULL
, LOR.S_PERMT_ADDR_CHG_DATE
, NULL
, LOR.S_DEFAULT_STATUS
, LOR.S_SIGNATURE_CODE
, LOR.CREATED_BY
, LOR.CREATION_DATE
, LOR.LAST_UPDATED_BY
, LOR.LAST_UPDATE_DATE
, LOR.LAST_UPDATE_LOGIN
, LOR.REQUEST_ID
, LOR.PROGRAM_APPLICATION_ID
, LOR.PROGRAM_ID
, LOR.PROGRAM_UPDATE_DATE
, LOR.ELEC_MPN_IND
, LOR.BORR_SIGN_IND
, LOR.STUD_SIGN_IND
, LOR.BORR_CREDIT_AUTH_CODE
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_S_DEFAULT_STATUS'
, LOR.S_DEFAULT_STATUS)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_P_DEFAULT_STATUS'
, LOR.P_DEFAULT_STATUS)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_DL_HP_UNSUB_ELIG'
, LOR.UNSUB_ELIG_FOR_HEAL)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_DL_DEP_UNSUB_ELIG'
, LOR.UNSUB_ELIG_FOR_DEPNT)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_AP_YES_NO'
, LOR.S_SIGNATURE_CODE)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_AP_YES_NO'
, LOR.P_SIGNATURE_CODE)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_PNOTE_TYPE'
, LOR.ELEC_MPN_IND)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_PNOTE_LINK'
, LOR.PNOTE_MPN_IND)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_AP_YES_NO'
, LOR.BORW_OUTSTD_LOAN_CODE)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_AP_YES_NO'
, LOR.GUARNT_ADJ_IND)
, LAR.LEGACY_RECORD_FLAG
, LOR.RELATIONSHIP_CD
, RECIP.RELATIONSHIP_CD_DESC
, LOR.INTEREST_REBATE_PERCENT_NUM
, LOR.CPS_TRANS_NUM
, LOR. ATD_ENTITY_ID_TXT
, LOR. REP_ENTITY_ID_TXT
, LOR.CRDT_DECISION_STATUS
, LOR.NOTE_MESSAGE
, LOR.BOOK_LOAN_AMT
, LOR.BOOK_LOAN_AMT_DATE
, LOR.PYMT_SERVICER_AMT
, LOR.PYMT_SERVICER_DATE
, IGF_AW_GEN.LOOKUP_DESC('IGF_SL_CL_ENROL_STATUS'
, LOR.ENROLLMENT_CODE)
, LOR.EXTERNAL_LOAN_ID_TXT
, LOR.DEFERMENT_REQUEST_CODE
, IGF_AW_GEN.LOOKUP_DESC('IGF_SL_CL_DEF_REQ_CODE'
, LOR.DEFERMENT_REQUEST_CODE)
, LOR.EFT_AUTHORIZATION_CODE
, IGF_AW_GEN.LOOKUP_DESC('IGF_SL_CL_EFT_AUTH_CODE'
, LOR.EFT_AUTHORIZATION_CODE)
, LOR.REQUESTED_LOAN_AMT
, LOR.ACTUAL_RECORD_TYPE_CODE
, IGF_AW_GEN.LOOKUP_DESC( 'IGF_SL_CL4_REC_ST_CODES'
, LOR.ACTUAL_RECORD_TYPE_CODE)
, LOR.REINSTATEMENT_AMT
, LOR.SCHOOL_USE_TXT
, LOR.LENDER_USE_TXT
, LOR.GUARANTOR_USE_TXT
, LOR.FLS_APPROVED_AMT
, LOR.FLU_APPROVED_AMT
, LOR.FLP_APPROVED_AMT
, LOR.ALT_APPROVED_AMT
, LOR.LOAN_APP_FORM_CODE
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_CL_APP_FORM_CODE'
, LOR.LOAN_APP_FORM_CODE)
, LOR.OVERRIDE_GRADE_LEVEL_CODE
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_GRADE_LEVEL'
, LOR.OVERRIDE_GRADE_LEVEL_CODE)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_DISCLOSURE_PRINT_IND'
, LOR.DISCLOSURE_PRINT_IND)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_CL4_REC_ST_CODES'
, LOR.CL_REC_STATUS )
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_CL_MPN_CONF_IND'
, LOR.MPN_CONFIRM_CODE)
, IGF_AW_GEN.LOOKUP_DESC ('IGF_SL_CL_APP_PHASE_CODES'
, LOR.APPL_LOAN_PHASE_CODE)
, LOR. B_ALIEN_REG_NUM_TXT
, LOR. ESIGN_SRC_TYP_CD
, LOR.ACAD_BEGIN_DATE
, LOR.ACAD_END_DATE
FROM IGF_SL_LOANS LAR
, IGF_SL_LOR LOR
, IGF_AW_AWARD AWDV
, IGF_AW_FUND_MAST FMAST
, IGF_AW_FUND_CAT FCAT
, IGS_CA_INST CI
, IGF_AP_FA_BASE_REC BASE
, IGF_SL_CL_RECIPIENT RECIP
WHERE LAR.LOAN_ID = LOR.LOAN_ID
AND LAR.AWARD_ID = AWDV.AWARD_ID
AND FMAST.FUND_ID = AWDV.FUND_ID
AND BASE.BASE_ID = AWDV.BASE_ID
AND CI.CAL_TYPE = BASE.CI_CAL_TYPE
AND CI.SEQUENCE_NUMBER = BASE.CI_SEQUENCE_NUMBER
AND FCAT.FUND_CODE = FMAST.FUND_CODE
AND LOR.RELATIONSHIP_CD=RECIP.RELATIONSHIP_CD(+)