IBY_EXTN_PMT_INSTR_V

Details

• Schema: FUSION

• Object owner: IBY

• Object type: VIEW

Columns

Name

ROW_ID TRXN_EXTENSION_ID PAYMENT_CHANNEL_CODE PAYMENT_CHANNEL_NAME INSTR_ASSIGNMENT_ID INSTRUMENT_TYPE INSTRUMENT_ID CURRENCY_CODE CARD_NUMBER CARD_NUMBER_HASH1 CARD_NUMBER_HASH2 CARD_EXPIRYDATE CARD_ISSUER_CODE CARD_ISSUER_NAME PURCHASECARD_SUBTYPE CARD_HOLDER_NAME ACCOUNT_NUMBER BANK_NUMBER INSTRUMENT_SECURITY_CODE VOICE_AUTHORIZATION_FLAG VOICE_AUTHORIZATION_DATE VOICE_AUTHORIZATION_CODE ORIGIN_APPLICATION_ID ORDER_ID PO_NUMBER PO_LINE_NUMBER TRXN_REF_NUMBER1 TRXN_REF_NUMBER2 ADDITIONAL_INFO PAYMENT_SYSTEM_ORDER_NUMBER BANK_NAME BANK_BRANCH_NAME DEBIT_AUTHORIZATION_ID

Query

SQL_Statement

SELECT x.rowid ROW_ID, x.trxn_extension_id TRXN_EXTENSION_ID, p.payment_channel_code PAYMENT_CHANNEL_CODE, pt.payment_channel_name PAYMENT_CHANNEL_NAME, x.instr_assignment_id INSTR_ASSIGNMENT_ID, instr.instrument_type INSTRUMENT_TYPE, instr.instrument_id INSTRUMENT_ID, instr.currency_code CURRENCY_CODE, instr.masked_cc_number CARD_NUMBER, instr.cc_number_hash1 CARD_NUMBER_HASH1, instr.cc_number_hash2 CARD_NUMBER_HASH2, '**/**' CARD_EXPIRYDATE, instr.card_issuer_code CARD_ISSUER_CODE, instr.card_issuer_name CARD_ISSUER_NAME, instr.purchasecard_subtype PURCHASECARD_SUBTYPE, instr.CARD_HOLDER_NAME CARD_HOLDER_NAME, instr.masked_bank_account_num ACCOUNT_NUMBER, instr.bank_or_branch_number BANK_NUMBER, DECODE( DECODE(x.instrument_security_code, NULL, NULL, x.instr_sec_code_length), NULL, NULL, 1, 'X', 2, 'XX', 3, 'XXX', 4, 'XXXX', 'XXXXX') INSTRUMENT_SECURITY_CODE, x.voice_authorization_flag VOICE_AUTHORIZATION_FLAG, x.voice_authorization_date VOICE_AUTHORIZATION_DATE, x.voice_authorization_code VOICE_AUTHORIZATION_CODE, x.origin_application_id ORIGIN_APPLICATION_ID, x.order_id ORDER_ID, x.po_number PO_NUMBER, x.po_line_number PO_LINE_NUMBER, x.trxn_ref_number1 TRXN_REF_NUMBER1, x.trxn_ref_number2 TRXN_REF_NUMBER2, x.additional_info ADDITIONAL_INFO, x.payment_system_order_number PAYMENT_SYSTEM_ORDER_NUMBER, instr.BANK_NAME BANK_NAME, instr.BANK_BRANCH_NAME BANK_BRANCH_NAME, x.debit_authorization_id DEBIT_AUTHORIZATION_ID FROM iby_fndcpt_tx_extensions x, fnd_application a, iby_fndcpt_pmt_chnnls_b p , iby_fndcpt_pmt_chnnls_tl pt , (SELECT u.instrument_type, u.instrument_id, NULL currency_code, c.masked_cc_number, c.cc_number_hash1, c.cc_number_hash2, c.card_issuer_code, it.card_issuer_name, c.purchasecard_subtype, lpad('*', LENGTH(NVL(c.chname, hzp.party_name)), '*') CARD_HOLDER_NAME, NULL masked_bank_account_num, NULL bank_or_branch_number, NULL BANK_NAME, NULL BANK_BRANCH_NAME, u.instrument_payment_use_id FROM iby_creditcard c , iby_creditcard_issuers_b i , iby_creditcard_issuers_tl it , iby_pmt_instr_uses_all u , hz_parties hzp WHERE u.instrument_type = 'CREDITCARD' AND u.instrument_id = c.instrid AND c.card_issuer_code = i.card_issuer_code(+) AND c.card_owner_id = hzp.party_id(+) AND I.card_issuer_code =IT.card_issuer_code(+) AND IT.language(+) =USERENV('LANG') UNION ALL SELECT u.instrument_type, u.instrument_id, b.currency_code, NULL masked_cc_number, NULL cc_number_hash1, NULL cc_number_hash2, NULL card_issuer_code, NULL card_issuer_name, NULL purchasecard_subtype, NULL CARD_HOLDER_NAME, b.masked_bank_account_num, bankprofile.bank_or_branch_number, bhzp.party_name BANK_NAME, brhzp.party_name BANK_BRANCH_NAME, u.instrument_payment_use_id FROM iby_ext_bank_accounts b , iby_pmt_instr_uses_all u , hz_parties bhzp , hz_parties brhzp , hz_organization_profiles bankprofile WHERE u.instrument_type = 'BANKACCOUNT' AND u.instrument_id = b.ext_bank_account_id AND b.bank_id =bhzp.party_id(+) AND b.branch_id =brhzp.party_id(+) AND bhzp.party_id =bankProfile.party_id(+) AND TRUNC(sysdate) BETWEEN TRUNC(bankprofile.effective_start_date(+)) AND NVL(TRUNC(bankprofile.effective_end_date(+)), sysdate + 1) ) instr WHERE x.origin_application_id = a.application_id AND x.instr_assignment_id = instr.instrument_payment_use_id(+) AND x.payment_channel_code = p.payment_channel_code AND P.payment_channel_code =PT.payment_channel_code AND PT.language =USERENV('LANG')