VIEW: APPS.IGFFV_FFELP_LOAN_ORIGINATIONS

Source

View Text - Preformatted

SELECT LOR.SCH_CERT_DATE, LOR.CL_SEQ_NUMBER, LOR.RELATIONSHIP_CD, '_LA:LOR.PNOTE_STATUS:IGF_LOOKUPS_VIEW:IGF_SL_PNOTE_STATUS:MEANING', '_LA:LOR.S_DEFAULT_STATUS:IGF_LOOKUPS_VIEW:IGF_SL_S_DEFAULT_STATUS:MEANING', '_LA:LOR.P_DEFAULT_STATUS:IGF_LOOKUPS_VIEW:IGF_SL_P_DEFAULT_STATUS:MEANING', '_LA:LOR.PRC_TYPE_CODE:IGF_LOOKUPS_VIEW:IGF_SL_PRC_TYPE_CODE:MEANING', '_LA:LOR.REC_TYPE_IND:IGF_LOOKUPS_VIEW:IGF_SL_REC_TYPE_IND:MEANING', '_LA:LOR.REQ_SERIAL_LOAN_CODE:IGF_LOOKUPS_VIEW:IGF_SL_SERIAL_LOAN_CODE:MEANING', '_LA:LOR.ACT_SERIAL_LOAN_CODE:IGF_LOOKUPS_VIEW:IGF_SL_ACT_SERIAL_LOAN_CODE:MEANING', '_LA:LOR.PNOTE_DELIVERY_CODE:IGF_LOOKUPS_VIEW:IGF_SL_PNOTE_DELIVERY:MEANING', '_LA:LOR.BORW_CONFIRM_IND:IGF_LOOKUPS_VIEW:IGF_SL_BORW_CONFIRM:MEANING', '_LA:LOR.GUARNT_STATUS_CODE:IGF_LOOKUPS_VIEW:IGF_SL_GUARNT_STATUS:MEANING', '_LA:LOR.LEND_STATUS_CODE:IGF_LOOKUPS_VIEW:IGF_SL_LEND_STATUS:MEANING', '_LA:LOR.CREDIT_OVERRIDE:IGF_LOOKUPS_VIEW:IGF_SL_CREDIT_OVERRIDE:MEANING', '_LA:LOR.GRADE_LEVEL_CODE:IGF_LOOKUPS_VIEW:IGF_SL_CL_GRADE_LEVEL:MEANING', '_LA:LOR.ENROLLMENT_CODE:IGF_LOOKUPS_VIEW:IGF_SL_CL_ENROL_STATUS:MEANING', '_LA:LOR.GUARNT_AMT_REDN_CODE:IGF_LOOKUPS_VIEW:IGF_SL_GUARNT_AMT_RED_CODE:MEANING', '_LA:LOR.GUARNT_ADJ_IND:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING', '_LA:LOR.S_SIGNATURE_CODE:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING', '_LA:LOR.P_SIGNATURE_CODE:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING', '_LA:LOR.BORW_OUTSTD_LOAN_CODE:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING', LOR.P_SIGNATURE_DATE, LOR.LAST_RESORT_LENDER, LOR.BORR_CREDIT_AUTH_CODE, LOR.STUD_SIGN_IND, LOR.BORR_SIGN_IND, LOR.BORW_INTEREST_IND, LOR.ACT_INTEREST_RATE, LOR.GUARANTEE_AMT, LOR.GUARANTEE_DATE, LOR.GUARNT_STATUS_DATE, LOR.PNOTE_STATUS_DATE, LOR.LEND_STATUS_DATE, LOR.CREDIT_DECISION_DATE, ALT.FED_STAFFORD_LOAN_DEBT, ALT.FED_SLS_DEBT, ALT.HEAL_DEBT, ALT.PERKINS_DEBT, ALT.OTHER_DEBT, ALT.BORW_GROSS_ANNUAL_SAL, ALT.BORW_OTHER_INCOME, ALT.STUDENT_MAJOR, ALT.STUD_MTH_HOUSING_PYMT, ALT.STUD_MTH_CRDTCARD_PYMT, ALT.STUD_MTH_AUTO_PYMT, ALT.STUD_MTH_ED_LOAN_PYMT, ALT.STUD_MTH_OTHER_PYMT, '_LA:ALT.INT_RATE_OPT:IGF_LOOKUPS_VIEW:IGF_SL_ALT_INT_RATE_OPTION:MEANING', '_LA:ALT.REPAYMENT_OPT_CODE:IGF_LOOKUPS_VIEW:IGF_SL_ALT_REPAY_OPTION:MEANING', '_LA:ALT.CRDT_UNDR_DIFFT_NAME:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING', HZ_STUD.PARTY_NUMBER, HZ_STUD.PERSON_FIRST_NAME, HZ_STUD.PERSON_LAST_NAME, HZ_STUD.PERSON_MIDDLE_NAME, HZ_STUD.ADDRESS1, HZ_STUD.ADDRESS2, HZ_STUD.ADDRESS3, HZ_STUD.ADDRESS4, HZ_STUD.CITY, HZ_STUD.STATE, HZ_STUD.POSTAL_CODE, HZ_STUD.EMAIL_ADDRESS, HZ_BORR.PARTY_NUMBER, HZ_BORR.PERSON_FIRST_NAME, HZ_BORR.PERSON_LAST_NAME, HZ_BORR.PERSON_MIDDLE_NAME, HZ_BORR.ADDRESS1, HZ_BORR.ADDRESS2, HZ_BORR.ADDRESS3, HZ_BORR.ADDRESS4, HZ_BORR.CITY, HZ_BORR.STATE, HZ_BORR.POSTAL_CODE, HZ_BORR.EMAIL_ADDRESS, RECIP.RELATIONSHIP_CD_DESC, RECIP.RECIPIENT_TYPE, FABASE.CI_CAL_TYPE, FABASE.CI_SEQUENCE_NUMBER, CA.ALTERNATE_CODE, LOANS.LOAN_NUMBER, LOANS.LOAN_PER_BEGIN_DATE, LOANS.LOAN_PER_END_DATE, '_LA:LOANS.LOAN_STATUS:IGF_LOOKUPS_VIEW:IGF_SL_LOAN_STATUS:MEANING', LOANS.LOAN_STATUS_DATE, LOANS.ACTIVE, LOANS.ACTIVE_DATE, AWD.ACCEPTED_AMT, FMAST.DESCRIPTION, LOR.LOAN_ID, LOR.P_PERSON_ID, ALT.ALBW_ID, RECIP.LENDER_ID, RECIP.LEND_NON_ED_BRC_ID, RECIP.GUARANTOR_ID, RECIP.RECIPIENT_ID, RECIP.RECIP_NON_ED_BRC_ID, FABASE.BASE_ID, FABASE.PERSON_ID, LOR.ORIGINATION_ID, LOR.CREATED_BY, LOR.CREATION_DATE, LOR.LAST_UPDATED_BY, LOR.LAST_UPDATE_DATE, LOR.B_ALIEN_REG_NUM_TXT, LOR.ESIGN_SRC_TYP_CD FROM IGF_SL_LOR_ALL LOR, IGF_SL_LOANS_ALL LOANS, IGF_AP_FA_BASE_REC_ALL FABASE, IGF_AW_AWARD_ALL AWD, IGF_AW_FUND_MAST_ALL FMAST, IGF_AW_FUND_CAT_ALL FCAT, IGF_SL_CL_RECIPIENT RECIP, HZ_PARTIES HZ_STUD, HZ_PARTIES HZ_BORR, IGS_CA_INST_ALL CA, IGF_SL_ALT_BORW_ALL ALT WHERE AWD.AWARD_ID = LOANS.AWARD_ID AND LOR.LOAN_ID = LOANS.LOAN_ID AND FABASE.BASE_ID = AWD.BASE_ID AND FMAST.FUND_ID = AWD.FUND_ID AND FCAT.FUND_CODE = FMAST.FUND_CODE AND FCAT.FED_FUND_CODE IN ( 'FLP','FLS','FLU', 'ALT') AND LOR.RELATIONSHIP_CD = RECIP.RELATIONSHIP_CD (+) AND FABASE.CI_CAL_TYPE = CA.CAL_TYPE AND FABASE.CI_SEQUENCE_NUMBER = CA.SEQUENCE_NUMBER AND HZ_STUD.PARTY_ID = FABASE.PERSON_ID AND LOR.P_PERSON_ID = HZ_BORR.PARTY_ID(+) AND LOR.LOAN_ID = ALT.LOAN_ID (+) WITH READ ONLY

View Text - HTML Formatted

SELECT LOR.SCH_CERT_DATE
, LOR.CL_SEQ_NUMBER
, LOR.RELATIONSHIP_CD
, '_LA:LOR.PNOTE_STATUS:IGF_LOOKUPS_VIEW:IGF_SL_PNOTE_STATUS:MEANING'
, '_LA:LOR.S_DEFAULT_STATUS:IGF_LOOKUPS_VIEW:IGF_SL_S_DEFAULT_STATUS:MEANING'
, '_LA:LOR.P_DEFAULT_STATUS:IGF_LOOKUPS_VIEW:IGF_SL_P_DEFAULT_STATUS:MEANING'
, '_LA:LOR.PRC_TYPE_CODE:IGF_LOOKUPS_VIEW:IGF_SL_PRC_TYPE_CODE:MEANING'
, '_LA:LOR.REC_TYPE_IND:IGF_LOOKUPS_VIEW:IGF_SL_REC_TYPE_IND:MEANING'
, '_LA:LOR.REQ_SERIAL_LOAN_CODE:IGF_LOOKUPS_VIEW:IGF_SL_SERIAL_LOAN_CODE:MEANING'
, '_LA:LOR.ACT_SERIAL_LOAN_CODE:IGF_LOOKUPS_VIEW:IGF_SL_ACT_SERIAL_LOAN_CODE:MEANING'
, '_LA:LOR.PNOTE_DELIVERY_CODE:IGF_LOOKUPS_VIEW:IGF_SL_PNOTE_DELIVERY:MEANING'
, '_LA:LOR.BORW_CONFIRM_IND:IGF_LOOKUPS_VIEW:IGF_SL_BORW_CONFIRM:MEANING'
, '_LA:LOR.GUARNT_STATUS_CODE:IGF_LOOKUPS_VIEW:IGF_SL_GUARNT_STATUS:MEANING'
, '_LA:LOR.LEND_STATUS_CODE:IGF_LOOKUPS_VIEW:IGF_SL_LEND_STATUS:MEANING'
, '_LA:LOR.CREDIT_OVERRIDE:IGF_LOOKUPS_VIEW:IGF_SL_CREDIT_OVERRIDE:MEANING'
, '_LA:LOR.GRADE_LEVEL_CODE:IGF_LOOKUPS_VIEW:IGF_SL_CL_GRADE_LEVEL:MEANING'
, '_LA:LOR.ENROLLMENT_CODE:IGF_LOOKUPS_VIEW:IGF_SL_CL_ENROL_STATUS:MEANING'
, '_LA:LOR.GUARNT_AMT_REDN_CODE:IGF_LOOKUPS_VIEW:IGF_SL_GUARNT_AMT_RED_CODE:MEANING'
, '_LA:LOR.GUARNT_ADJ_IND:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING'
, '_LA:LOR.S_SIGNATURE_CODE:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING'
, '_LA:LOR.P_SIGNATURE_CODE:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING'
, '_LA:LOR.BORW_OUTSTD_LOAN_CODE:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING'
, LOR.P_SIGNATURE_DATE
, LOR.LAST_RESORT_LENDER
, LOR.BORR_CREDIT_AUTH_CODE
, LOR.STUD_SIGN_IND
, LOR.BORR_SIGN_IND
, LOR.BORW_INTEREST_IND
, LOR.ACT_INTEREST_RATE
, LOR.GUARANTEE_AMT
, LOR.GUARANTEE_DATE
, LOR.GUARNT_STATUS_DATE
, LOR.PNOTE_STATUS_DATE
, LOR.LEND_STATUS_DATE
, LOR.CREDIT_DECISION_DATE
, ALT.FED_STAFFORD_LOAN_DEBT
, ALT.FED_SLS_DEBT
, ALT.HEAL_DEBT
, ALT.PERKINS_DEBT
, ALT.OTHER_DEBT
, ALT.BORW_GROSS_ANNUAL_SAL
, ALT.BORW_OTHER_INCOME
, ALT.STUDENT_MAJOR
, ALT.STUD_MTH_HOUSING_PYMT
, ALT.STUD_MTH_CRDTCARD_PYMT
, ALT.STUD_MTH_AUTO_PYMT
, ALT.STUD_MTH_ED_LOAN_PYMT
, ALT.STUD_MTH_OTHER_PYMT
, '_LA:ALT.INT_RATE_OPT:IGF_LOOKUPS_VIEW:IGF_SL_ALT_INT_RATE_OPTION:MEANING'
, '_LA:ALT.REPAYMENT_OPT_CODE:IGF_LOOKUPS_VIEW:IGF_SL_ALT_REPAY_OPTION:MEANING'
, '_LA:ALT.CRDT_UNDR_DIFFT_NAME:IGF_LOOKUPS_VIEW:IGF_AP_YES_NO:MEANING'
, HZ_STUD.PARTY_NUMBER
, HZ_STUD.PERSON_FIRST_NAME
, HZ_STUD.PERSON_LAST_NAME
, HZ_STUD.PERSON_MIDDLE_NAME
, HZ_STUD.ADDRESS1
, HZ_STUD.ADDRESS2
, HZ_STUD.ADDRESS3
, HZ_STUD.ADDRESS4
, HZ_STUD.CITY
, HZ_STUD.STATE
, HZ_STUD.POSTAL_CODE
, HZ_STUD.EMAIL_ADDRESS
, HZ_BORR.PARTY_NUMBER
, HZ_BORR.PERSON_FIRST_NAME
, HZ_BORR.PERSON_LAST_NAME
, HZ_BORR.PERSON_MIDDLE_NAME
, HZ_BORR.ADDRESS1
, HZ_BORR.ADDRESS2
, HZ_BORR.ADDRESS3
, HZ_BORR.ADDRESS4
, HZ_BORR.CITY
, HZ_BORR.STATE
, HZ_BORR.POSTAL_CODE
, HZ_BORR.EMAIL_ADDRESS
, RECIP.RELATIONSHIP_CD_DESC
, RECIP.RECIPIENT_TYPE
, FABASE.CI_CAL_TYPE
, FABASE.CI_SEQUENCE_NUMBER
, CA.ALTERNATE_CODE
, LOANS.LOAN_NUMBER
, LOANS.LOAN_PER_BEGIN_DATE
, LOANS.LOAN_PER_END_DATE
, '_LA:LOANS.LOAN_STATUS:IGF_LOOKUPS_VIEW:IGF_SL_LOAN_STATUS:MEANING'
, LOANS.LOAN_STATUS_DATE
, LOANS.ACTIVE
, LOANS.ACTIVE_DATE
, AWD.ACCEPTED_AMT
, FMAST.DESCRIPTION
, LOR.LOAN_ID
, LOR.P_PERSON_ID
, ALT.ALBW_ID
, RECIP.LENDER_ID
, RECIP.LEND_NON_ED_BRC_ID
, RECIP.GUARANTOR_ID
, RECIP.RECIPIENT_ID
, RECIP.RECIP_NON_ED_BRC_ID
, FABASE.BASE_ID
, FABASE.PERSON_ID
, LOR.ORIGINATION_ID
, LOR.CREATED_BY
, LOR.CREATION_DATE
, LOR.LAST_UPDATED_BY
, LOR.LAST_UPDATE_DATE
, LOR.B_ALIEN_REG_NUM_TXT
, LOR.ESIGN_SRC_TYP_CD
FROM IGF_SL_LOR_ALL LOR
, IGF_SL_LOANS_ALL LOANS
, IGF_AP_FA_BASE_REC_ALL FABASE
, IGF_AW_AWARD_ALL AWD
, IGF_AW_FUND_MAST_ALL FMAST
, IGF_AW_FUND_CAT_ALL FCAT
, IGF_SL_CL_RECIPIENT RECIP
, HZ_PARTIES HZ_STUD
, HZ_PARTIES HZ_BORR
, IGS_CA_INST_ALL CA
, IGF_SL_ALT_BORW_ALL ALT
WHERE AWD.AWARD_ID = LOANS.AWARD_ID
AND LOR.LOAN_ID = LOANS.LOAN_ID
AND FABASE.BASE_ID = AWD.BASE_ID
AND FMAST.FUND_ID = AWD.FUND_ID
AND FCAT.FUND_CODE = FMAST.FUND_CODE
AND FCAT.FED_FUND_CODE IN ( 'FLP'
, 'FLS'
, 'FLU'
, 'ALT')
AND LOR.RELATIONSHIP_CD = RECIP.RELATIONSHIP_CD (+)
AND FABASE.CI_CAL_TYPE = CA.CAL_TYPE
AND FABASE.CI_SEQUENCE_NUMBER = CA.SEQUENCE_NUMBER
AND HZ_STUD.PARTY_ID = FABASE.PERSON_ID
AND LOR.P_PERSON_ID = HZ_BORR.PARTY_ID(+)
AND LOR.LOAN_ID = ALT.LOAN_ID (+) WITH READ ONLY