VIEW: APPS.IGSFV_PERSON_DEPOSITS

Source

View Text - Preformatted

SELECT cr.credit_number, '_LA:CR.STATUS:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_STATUS:MEANING', '_LA:CR.CREDIT_INSTRUMENT:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_INSTRUMENT:MEANING', cr.description, cr.amount, cr.unapplied_amount, cr.currency_cd, cr.exchange_rate, cr.transaction_date, cr.effective_date, cr.check_number, '_LA:CR.CREDIT_CARD_CODE:IGS_LOOKUP_VALUES:IGS_CREDIT_CARDS:MEANING', cr.credit_card_holder_name, cr.credit_card_number, cr.credit_card_expiration_date, cr.credit_card_approval_code, '_LA:CR.CREDIT_CARD_STATUS_CODE:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_CARD_STATUS:MEANING', cr.credit_card_payee_cd, cr.credit_card_tangible_cd, cr.awd_yr_cal_type, cr.awd_yr_ci_sequence_number, cr.fee_cal_type, cr.fee_ci_sequence_number, cr.gl_date, cr.source_transaction_type, cr.source_transaction_ref, cr.reversal_date, '_LA:CR.REVERSAL_REASON_CODE:IGS_LOOKUP_VALUES:IGS_FI_REVERSAL_REASON:MEANING', cr.reversal_comments, cr.receipt_lockbox_number, cr.batch_name, cr.deposit_date, '_LA:CA.STATUS:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_STATUS:MEANING', ca.transaction_date, ca.amount, ca.dr_account_cd, ca.cr_account_cd, '_KF:SQLGL:GL#:gcc1', '_KF:SQLGL:GL#:gcc2', ca.bill_number, ca.bill_date, ca.gl_date, ca.gl_posted_date, '_DF:IGS:IGS_FI_CREDITS_ALL_FLEX:cr', pa.party_number, pa.party_name, ib.name, ci1.start_dt, ci1.end_dt, ci1.description, ci1.alternate_code, ci2.start_dt, ci2.end_dt, ci2.description, ci2.alternate_code, cp.credit_type_name, '_LA:cp.CREDIT_CLASS:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_CLASS:MEANING', lo.lockbox_desc, ac1.description, ac2.description, bi.cut_off_date, po.batch_name, cr.credit_id, ca.credit_activity_id, cr.party_id, cr.credit_type_id, cr.merchant_id, cr.lockbox_interface_id, ca.dr_gl_ccid, ca.cr_gl_ccid, ca.bill_id, ca.posting_id, ca.posting_control_id, ca.creation_date, ca.created_by, ca.last_update_date, ca.last_updated_by FROM igs_fi_credits_all cr, igs_fi_cr_activities ca, hz_parties pa, igs_ca_inst_all ci1, igs_ca_inst_all ci2, igs_fi_cr_types_all cp, igs_fi_acc_all ac1, igs_fi_acc_all ac2, igs_fi_bill_all bi, igs_fi_posting_int_all po, igs_fi_lockboxes lo, gl_code_combinations gcc1, gl_code_combinations gcc2, iby_payee ib WHERE cr.credit_id = ca.credit_id AND cr.party_id = pa.party_id AND cr.credit_type_id = cp.credit_type_id AND cp.credit_class in ('OTHDEPOSIT','ENRDEPOSIT') AND cr.awd_yr_cal_type = ci1.cal_type(+) AND cr.awd_yr_ci_sequence_number = ci1.sequence_number(+) AND cr.fee_cal_type = ci2.cal_type(+) AND cr.fee_ci_sequence_number = ci2.sequence_number(+) AND cr.receipt_lockbox_number = lo.lockbox_name(+) AND ca.dr_account_cd = ac1.account_cd(+) AND ca.cr_account_cd = ac2.account_cd(+) AND ca.bill_id = bi.bill_id(+) AND ca.posting_id = po.posting_id(+) AND ca.dr_gl_ccid = gcc1.code_combination_id(+) AND ca.cr_gl_ccid = gcc2.code_combination_id(+) AND cr.credit_card_payee_cd = ib.payeeid(+) WITH READ ONLY

View Text - HTML Formatted

SELECT CR.CREDIT_NUMBER
, '_LA:CR.STATUS:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_STATUS:MEANING'
, '_LA:CR.CREDIT_INSTRUMENT:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_INSTRUMENT:MEANING'
, CR.DESCRIPTION
, CR.AMOUNT
, CR.UNAPPLIED_AMOUNT
, CR.CURRENCY_CD
, CR.EXCHANGE_RATE
, CR.TRANSACTION_DATE
, CR.EFFECTIVE_DATE
, CR.CHECK_NUMBER
, '_LA:CR.CREDIT_CARD_CODE:IGS_LOOKUP_VALUES:IGS_CREDIT_CARDS:MEANING'
, CR.CREDIT_CARD_HOLDER_NAME
, CR.CREDIT_CARD_NUMBER
, CR.CREDIT_CARD_EXPIRATION_DATE
, CR.CREDIT_CARD_APPROVAL_CODE
, '_LA:CR.CREDIT_CARD_STATUS_CODE:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_CARD_STATUS:MEANING'
, CR.CREDIT_CARD_PAYEE_CD
, CR.CREDIT_CARD_TANGIBLE_CD
, CR.AWD_YR_CAL_TYPE
, CR.AWD_YR_CI_SEQUENCE_NUMBER
, CR.FEE_CAL_TYPE
, CR.FEE_CI_SEQUENCE_NUMBER
, CR.GL_DATE
, CR.SOURCE_TRANSACTION_TYPE
, CR.SOURCE_TRANSACTION_REF
, CR.REVERSAL_DATE
, '_LA:CR.REVERSAL_REASON_CODE:IGS_LOOKUP_VALUES:IGS_FI_REVERSAL_REASON:MEANING'
, CR.REVERSAL_COMMENTS
, CR.RECEIPT_LOCKBOX_NUMBER
, CR.BATCH_NAME
, CR.DEPOSIT_DATE
, '_LA:CA.STATUS:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_STATUS:MEANING'
, CA.TRANSACTION_DATE
, CA.AMOUNT
, CA.DR_ACCOUNT_CD
, CA.CR_ACCOUNT_CD
, '_KF:SQLGL:GL#:GCC1'
, '_KF:SQLGL:GL#:GCC2'
, CA.BILL_NUMBER
, CA.BILL_DATE
, CA.GL_DATE
, CA.GL_POSTED_DATE
, '_DF:IGS:IGS_FI_CREDITS_ALL_FLEX:CR'
, PA.PARTY_NUMBER
, PA.PARTY_NAME
, IB.NAME
, CI1.START_DT
, CI1.END_DT
, CI1.DESCRIPTION
, CI1.ALTERNATE_CODE
, CI2.START_DT
, CI2.END_DT
, CI2.DESCRIPTION
, CI2.ALTERNATE_CODE
, CP.CREDIT_TYPE_NAME
, '_LA:CP.CREDIT_CLASS:IGS_LOOKUP_VALUES:IGS_FI_CREDIT_CLASS:MEANING'
, LO.LOCKBOX_DESC
, AC1.DESCRIPTION
, AC2.DESCRIPTION
, BI.CUT_OFF_DATE
, PO.BATCH_NAME
, CR.CREDIT_ID
, CA.CREDIT_ACTIVITY_ID
, CR.PARTY_ID
, CR.CREDIT_TYPE_ID
, CR.MERCHANT_ID
, CR.LOCKBOX_INTERFACE_ID
, CA.DR_GL_CCID
, CA.CR_GL_CCID
, CA.BILL_ID
, CA.POSTING_ID
, CA.POSTING_CONTROL_ID
, CA.CREATION_DATE
, CA.CREATED_BY
, CA.LAST_UPDATE_DATE
, CA.LAST_UPDATED_BY
FROM IGS_FI_CREDITS_ALL CR
, IGS_FI_CR_ACTIVITIES CA
, HZ_PARTIES PA
, IGS_CA_INST_ALL CI1
, IGS_CA_INST_ALL CI2
, IGS_FI_CR_TYPES_ALL CP
, IGS_FI_ACC_ALL AC1
, IGS_FI_ACC_ALL AC2
, IGS_FI_BILL_ALL BI
, IGS_FI_POSTING_INT_ALL PO
, IGS_FI_LOCKBOXES LO
, GL_CODE_COMBINATIONS GCC1
, GL_CODE_COMBINATIONS GCC2
, IBY_PAYEE IB
WHERE CR.CREDIT_ID = CA.CREDIT_ID
AND CR.PARTY_ID = PA.PARTY_ID
AND CR.CREDIT_TYPE_ID = CP.CREDIT_TYPE_ID
AND CP.CREDIT_CLASS IN ('OTHDEPOSIT'
, 'ENRDEPOSIT')
AND CR.AWD_YR_CAL_TYPE = CI1.CAL_TYPE(+)
AND CR.AWD_YR_CI_SEQUENCE_NUMBER = CI1.SEQUENCE_NUMBER(+)
AND CR.FEE_CAL_TYPE = CI2.CAL_TYPE(+)
AND CR.FEE_CI_SEQUENCE_NUMBER = CI2.SEQUENCE_NUMBER(+)
AND CR.RECEIPT_LOCKBOX_NUMBER = LO.LOCKBOX_NAME(+)
AND CA.DR_ACCOUNT_CD = AC1.ACCOUNT_CD(+)
AND CA.CR_ACCOUNT_CD = AC2.ACCOUNT_CD(+)
AND CA.BILL_ID = BI.BILL_ID(+)
AND CA.POSTING_ID = PO.POSTING_ID(+)
AND CA.DR_GL_CCID = GCC1.CODE_COMBINATION_ID(+)
AND CA.CR_GL_CCID = GCC2.CODE_COMBINATION_ID(+)
AND CR.CREDIT_CARD_PAYEE_CD = IB.PAYEEID(+) WITH READ ONLY