VIEW: APPS.IGS_FI_FEE_ASS_DEBT_V

Source

View Text - Preformatted

SELECT fas.person_id, ftci.fin_cal_type, ftci.fin_ci_sequence_number, fas.FEE_TYPE, fas.fee_cal_type, fas.fee_ci_sequence_number, fas.FEE_CAT, fas.course_cd, MAX(fas.transaction_dt) last_transaction_dt, MAX(fas.effective_dt) last_effective_assessment_dt, MAX(fas.notification_dt) last_notification_dt, SUM(fas.transaction_amount) assessment_amount, fas.currency_cd, CTRL.currency_cd local_currency_cd, SUM(fas.transaction_amount/fas.exchange_rate) local_assessment_amount, SUBSTR(IGS_FI_GEN_001.finp_get_fas_man_ind( fas.person_id, fas.FEE_TYPE, fas.fee_cal_type, fas.fee_ci_sequence_number, fas.FEE_CAT, fas.course_cd, 'DEBT'),1,1) manual_entry_ind FROM IGS_FI_FEE_AS fas, IGS_FI_F_TYP_CA_INST ftci, IGS_FI_CONTROL CTRL WHERE fas.S_TRANSACTION_TYPE in (SELECT strty.lookup_code FROM IGS_LOOKUPS_VIEW strty WHERE strty.lookup_code = fas.S_TRANSACTION_TYPE AND strty.transaction_cat = 'DEBT' AND strty.lookup_type = 'TRANSACTION_TYPE') AND fas.logical_delete_dt IS NULL AND ftci.FEE_TYPE = fas.FEE_TYPE AND ftci.fee_cal_type = fas.fee_cal_type AND ftci.fee_ci_sequence_number = fas.fee_ci_sequence_number GROUP BY fas.person_id, ftci.fin_cal_type, ftci.fin_ci_sequence_number, fas.FEE_TYPE, fas.fee_cal_type, fas.fee_ci_sequence_number, fas.FEE_CAT, fas.currency_cd, fas.course_cd, ctrl.currency_cd

View Text - HTML Formatted

SELECT FAS.PERSON_ID
, FTCI.FIN_CAL_TYPE
, FTCI.FIN_CI_SEQUENCE_NUMBER
, FAS.FEE_TYPE
, FAS.FEE_CAL_TYPE
, FAS.FEE_CI_SEQUENCE_NUMBER
, FAS.FEE_CAT
, FAS.COURSE_CD
, MAX(FAS.TRANSACTION_DT) LAST_TRANSACTION_DT
, MAX(FAS.EFFECTIVE_DT) LAST_EFFECTIVE_ASSESSMENT_DT
, MAX(FAS.NOTIFICATION_DT) LAST_NOTIFICATION_DT
, SUM(FAS.TRANSACTION_AMOUNT) ASSESSMENT_AMOUNT
, FAS.CURRENCY_CD
, CTRL.CURRENCY_CD LOCAL_CURRENCY_CD
, SUM(FAS.TRANSACTION_AMOUNT/FAS.EXCHANGE_RATE) LOCAL_ASSESSMENT_AMOUNT
, SUBSTR(IGS_FI_GEN_001.FINP_GET_FAS_MAN_IND( FAS.PERSON_ID
, FAS.FEE_TYPE
, FAS.FEE_CAL_TYPE
, FAS.FEE_CI_SEQUENCE_NUMBER
, FAS.FEE_CAT
, FAS.COURSE_CD
, 'DEBT')
, 1
, 1) MANUAL_ENTRY_IND
FROM IGS_FI_FEE_AS FAS
, IGS_FI_F_TYP_CA_INST FTCI
, IGS_FI_CONTROL CTRL
WHERE FAS.S_TRANSACTION_TYPE IN (SELECT STRTY.LOOKUP_CODE
FROM IGS_LOOKUPS_VIEW STRTY
WHERE STRTY.LOOKUP_CODE = FAS.S_TRANSACTION_TYPE
AND STRTY.TRANSACTION_CAT = 'DEBT'
AND STRTY.LOOKUP_TYPE = 'TRANSACTION_TYPE')
AND FAS.LOGICAL_DELETE_DT IS NULL
AND FTCI.FEE_TYPE = FAS.FEE_TYPE
AND FTCI.FEE_CAL_TYPE = FAS.FEE_CAL_TYPE
AND FTCI.FEE_CI_SEQUENCE_NUMBER = FAS.FEE_CI_SEQUENCE_NUMBER GROUP BY FAS.PERSON_ID
, FTCI.FIN_CAL_TYPE
, FTCI.FIN_CI_SEQUENCE_NUMBER
, FAS.FEE_TYPE
, FAS.FEE_CAL_TYPE
, FAS.FEE_CI_SEQUENCE_NUMBER
, FAS.FEE_CAT
, FAS.CURRENCY_CD
, FAS.COURSE_CD
, CTRL.CURRENCY_CD