[Home] [Help]
| Product: | IGS - Student System (Obsolete) |
|---|---|
| Description: | This is a multi-org view based on org filtered view IGS_FI_CREDITS and join with view IGS_LOOKUPS_VIEW and IGS_FI_CR_TYPES view to resolve Lookup Meaning and Credit Type Names from IDs |
| Implementation/DBA Data: | Not implemented in this database |
SELECT CRD.ROWID
, CRD.CREDIT_ID
, CRD.STATUS
, LKP.MEANING
, CRD.CREDIT_NUMBER
, LKV1.MEANING
, CRD.CREDIT_SOURCE
, CRD.PARTY_ID
, PAR.PERSON_NUMBER
, PAR.FULL_NAME
, PAR.API_PERSON_ID
, CRD.CREDIT_TYPE_ID
, CT.CREDIT_TYPE_NAME
, LKV2.MEANING
, CRD.CREDIT_INSTRUMENT
, CRD.DESCRIPTION
, CRD.AMOUNT
, CRD.CURRENCY_CD
, CRD.EXCHANGE_RATE
, CRD.TRANSACTION_DATE
, CRD.EFFECTIVE_DATE
, CRD.REVERSAL_DATE
, CRD.REVERSAL_REASON_CODE
, LKV5.MEANING
, CRD.REVERSAL_COMMENTS
, CRD.UNAPPLIED_AMOUNT
, CRD.SOURCE_TRANSACTION_ID
, CRD.SUBACCOUNT_ID
, NULL
, CRD.RECEIPT_LOCKBOX_NUMBER
, CRD.MERCHANT_ID
, LKV4.MEANING
, CT.CREDIT_CLASS
, LKV3.MEANING
, CRD.CREDIT_CARD_CODE
, CRD.CREDIT_CARD_HOLDER_NAME
, CRD.CREDIT_CARD_NUMBER
, CRD.CREDIT_CARD_EXPIRATION_DATE
, CRD.CREDIT_CARD_STATUS_CODE
, LKV7.MEANING
, CRD.CREDIT_CARD_PAYEE_CD
, CRD.CREDIT_CARD_TANGIBLE_CD
, CRD.CREDIT_CARD_APPROVAL_CODE
, CRD.AWD_YR_CAL_TYPE
, CRD.AWD_YR_CI_SEQUENCE_NUMBER
, CRD.FEE_CAL_TYPE
, CRD.FEE_CI_SEQUENCE_NUMBER
, INST.START_DT
, INST.END_DT
, CRD.ORG_ID
, CRD.ATTRIBUTE_CATEGORY
, CRD.ATTRIBUTE1
, CRD.ATTRIBUTE2
, CRD.ATTRIBUTE3
, CRD.ATTRIBUTE4
, CRD.ATTRIBUTE5
, CRD.ATTRIBUTE6
, CRD.ATTRIBUTE7
, CRD.ATTRIBUTE8
, CRD.ATTRIBUTE9
, CRD.ATTRIBUTE10
, CRD.ATTRIBUTE11
, CRD.ATTRIBUTE12
, CRD.ATTRIBUTE13
, CRD.ATTRIBUTE14
, CRD.ATTRIBUTE15
, CRD.ATTRIBUTE16
, CRD.ATTRIBUTE17
, CRD.ATTRIBUTE18
, CRD.ATTRIBUTE19
, CRD.ATTRIBUTE20
, CRD.LAST_UPDATED_BY
, CRD.LAST_UPDATE_LOGIN
, CRD.LAST_UPDATE_DATE
, CRD.CREATED_BY
, CRD.CREATION_DATE
, CRD.REQUEST_ID
, CRD.PROGRAM_APPLICATION_ID
, CRD.PROGRAM_ID
, CRD.PROGRAM_UPDATE_DATE
, CRD.GL_DATE
, CRD.CHECK_NUMBER
, CRD.SOURCE_TRANSACTION_TYPE
, LKV6.MEANING
, CRD.SOURCE_TRANSACTION_REF
, CRD.LOCKBOX_INTERFACE_ID
, CRD.BATCH_NAME
, CRD.DEPOSIT_DATE
, CRD.SOURCE_INVOICE_ID
, CRD.TAX_YEAR_CODE
, CRD.WAIVER_NAME
FROM IGS_FI_CREDITS CRD
, IGS_FI_PARTIES_V PAR
, IGF_LOOKUPS_VIEW LKV1
, IGS_LOOKUP_VALUES LKV2
, IGS_LOOKUP_VALUES LKV3
, IGS_LOOKUP_VALUES LKV4
, IGS_FI_CR_TYPES CT
, IGS_CA_INST INST
, IGS_LOOKUP_VALUES LKP
, IGS_LOOKUP_VALUES LKV5
, IGS_LOOKUP_VALUES LKV6
, IGS_LOOKUP_VALUES LKV7
WHERE CRD.PARTY_ID = PAR.PERSON_ID
AND CRD.CREDIT_SOURCE = LKV1.LOOKUP_CODE (+)
AND LKV1.LOOKUP_TYPE(+) = 'IGF_AW_FED_FUND' AND CRD.CREDIT_INSTRUMENT = LKV2.LOOKUP_CODE AND LKV2.LOOKUP_TYPE = 'IGS_FI_CREDIT_INSTRUMENT' AND CRD.CREDIT_TYPE_ID = CT.CREDIT_TYPE_ID AND CRD.CREDIT_CARD_CODE=LKV3.LOOKUP_CODE(+)
AND LKV3.LOOKUP_TYPE(+)='IGS_CREDIT_CARDS'
AND CT.CREDIT_CLASS=LKV4.LOOKUP_CODE
AND LKV4.LOOKUP_TYPE='IGS_FI_CREDIT_CLASS'
AND CRD.FEE_CAL_TYPE = INST.CAL_TYPE (+)
AND CRD.FEE_CI_SEQUENCE_NUMBER = INST.SEQUENCE_NUMBER (+)
AND CRD.STATUS = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'IGS_FI_CREDIT_STATUS'
AND CRD.REVERSAL_REASON_CODE = LKV5.LOOKUP_CODE(+)
AND LKV5.LOOKUP_TYPE (+) = 'IGS_FI_REVERSAL_REASON'
AND CRD.SOURCE_TRANSACTION_TYPE = LKV6.LOOKUP_CODE(+)
AND LKV6.LOOKUP_TYPE (+) = 'IGS_FI_SOURCE_TRANSACTION_REF'
AND LKV7.LOOKUP_TYPE (+) ='IGS_FI_CREDIT_CARD_STATUS'
AND CRD.CREDIT_CARD_STATUS_CODE = LKV7.LOOKUP_CODE(+)