IBY_TRXN_EXTENSIONS

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
ROW_ID TRXN_EXTENSION_ID PAYMENT_CHANNEL_CODE PAYMENT_CHANNEL_NAME EXT_PAYER_ID 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 PAYMENT_SYSTEM_ORDER_NUMBER ADDITIONAL_INFO AUTHORIZED_FLAG AUTHORIZATION_STATUS AUTH_PAYSYS_CODE AUTH_PAYSYS_MSG SETTLED_FLAG SETTLEMENT_STATUS SETTLE_PAYSYS_CODE SETTLE_PAYSYS_MSG RETURNED_FLAG RETURN_STATUS RETURN_PAYSYS_CODE RETURN_PAYSYS_MSG BANK_NAME BANK_BRANCH_NAME DEBIT_AUTHORIZATION_ID DEBIT_AUTH_LAST_TRX_FLAG

Name

ROW_ID

TRXN_EXTENSION_ID

PAYMENT_CHANNEL_CODE

PAYMENT_CHANNEL_NAME

EXT_PAYER_ID

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

PAYMENT_SYSTEM_ORDER_NUMBER

ADDITIONAL_INFO

AUTHORIZED_FLAG

AUTHORIZATION_STATUS

AUTH_PAYSYS_CODE

AUTH_PAYSYS_MSG

SETTLED_FLAG

SETTLEMENT_STATUS

SETTLE_PAYSYS_CODE

SETTLE_PAYSYS_MSG

RETURNED_FLAG

RETURN_STATUS

RETURN_PAYSYS_CODE

RETURN_PAYSYS_MSG

BANK_NAME

BANK_BRANCH_NAME

DEBIT_AUTHORIZATION_ID

DEBIT_AUTH_LAST_TRX_FLAG

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.ext_payer_id ext_payer_id, x.instr_assignment_id INSTR_ASSIGNMENT_ID, NVL(u.instrument_type,p.instrument_type) INSTRUMENT_TYPE, u.instrument_id INSTRUMENT_ID, b.currency_code CURRENCY_CODE, c.masked_cc_number CARD_NUMBER, c.cc_number_hash1 CARD_NUMBER_HASH1, c.cc_number_hash2 CARD_NUMBER_HASH2, c.expirydate CARD_EXPIRYDATE, c.card_issuer_code CARD_ISSUER_CODE, IT.card_issuer_name CARD_ISSUER_NAME, c.purchasecard_subtype PURCHASECARD_SUBTYPE, NVL(c.chname,hzp.party_name) CARD_HOLDER_NAME, b.masked_bank_account_num ACCOUNT_NUMBER, bankProfile.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.payment_system_order_number PAYMENT_SYSTEM_ORDER_NUMBER, x.additional_info ADDITIONAL_INFO, DECODE( authtxn.status, NULL,'N', 'Y' ) AUTHORIZED_FLAG, DECODE( authtxn.status, 0,'AUTH_SUCCESS', 100,'AUTH_PENDING', null) AUTHORIZATION_STATUS, authtxn.bepcode AUTH_PAYSYS_CODE, authtxn.bepmessage AUTH_PAYSYS_MSG, DECODE( settletxn.status, NULL,'N', 'Y' ) SETTLED_FLAG, DECODE( settletxn.status, 0,'SETTLEMENT_SUCCESS', 100,'SETTLEMENT_PENDING', null) SETTLEMENT_STATUS, settletxn.bepcode SETTLE_PAYSYS_CODE, settletxn.bepmessage SETTLE_PAYSYS_MSG, DECODE( returntxn.status, NULL,'N', 'Y' ) RETURNED_FLAG, DECODE( returntxn.status, 0,'RETURN_SUCCESS', 100,'RETURN_PENDING', null) RETURN_STATUS, returntxn.bepcode RETURN_PAYSYS_CODE, returntxn.bepmessage RETURN_PAYSYS_MSG, bhzp.party_name BANK_NAME, brhzp.party_name BANK_BRANCH_NAME, x.debit_authorization_id DEBIT_AUTHORIZATION_ID, x.debit_auth_last_trx_flag DEBIT_AUTH_LAST_TRX_FLAG FROM IBY_CREDITCARD C, IBY_CREDITCARD_ISSUERS_B I, IBY_CREDITCARD_ISSUERS_TL IT, IBY_EXT_BANK_ACCOUNTS B, IBY_FNDCPT_PMT_CHNNLS_B P, IBY_FNDCPT_PMT_CHNNLS_TL PT, IBY_FNDCPT_TX_EXTENSIONS X, IBY_PMT_INSTR_USES_ALL U, HZ_PARTIES HZP, FND_APPLICATION A, HZ_PARTIES bhzp, HZ_PARTIES brhzp, hz_organization_profiles bankProfile, ( SELECT op.trxn_extension_id, summ.status, summ.bepcode, summ.bepmessage, summ.last_update_date FROM iby_trxn_summaries_all summ, iby_fndcpt_tx_operations op WHERE summ.transactionid = op.transactionid AND reqtype = 'ORAPMTREQ' AND status IN (0,100) AND trxntypeid IN (2,3,20) AND summ.last_update_date = (SELECT MAX(int_summ.last_update_date) FROM iby_trxn_summaries_all int_summ WHERE reqtype = 'ORAPMTREQ' AND status IN (0,100) AND trxntypeid IN (2,3,20) AND int_summ.transactionid in ( select transactionid from iby_fndcpt_tx_operations op1 where op1.trxn_extension_id = op.trxn_extension_id ) ) ) authtxn, (SELECT op.trxn_extension_id, summ.status,summ.bepcode,summ.bepmessage, Rank() over (PARTITION BY Op.Trxn_Extension_Id ORDER BY op.Transactionid ) rank FROM iby_trxn_summaries_all summ, iby_fndcpt_tx_operations op WHERE summ.transactionid = op.transactionid AND ((instrtype IN ('CREDITCARD', 'PURCHASECARD') AND (reqtype = 'ORAPMTCAPTURE' OR trxntypeid = 3 ) ) OR (instrtype = 'BANKACCOUNT' AND reqtype = 'ORAPMTBATCHREQ') OR (instrtype = 'PINLESSDEBITCARD' AND reqtype = 'ORAPMTREQ')) AND status IN (0,100) ) settletxn, (SELECT op.trxn_extension_id, summ.status,summ.bepcode,summ.bepmessage FROM iby_trxn_summaries_all summ, iby_fndcpt_tx_operations op WHERE summ.transactionid = op.transactionid AND ((reqtype = 'ORAPMTRETURN') OR (reqtype = 'ORAPMTCREDIT')) AND status IN (0,100) AND summ.last_update_date= ( SELECT MAX(summ1.last_update_date) FROM iby_trxn_summaries_all summ1 WHERE ((reqtype = 'ORAPMTRETURN') OR (reqtype = 'ORAPMTCREDIT')) AND status IN (0,100) AND summ1.transactionid in (select transactionid from iby_fndcpt_tx_operations op1 where op1.trxn_extension_id = op.trxn_extension_id ) ) ) returntxn WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+)) AND (DECODE(u.instrument_type, 'CREDITCARD',u.instrument_id, NULL) = c.instrid(+)) AND (DECODE(u.instrument_type, 'BANKACCOUNT',u.instrument_id, NULL) = b.ext_bank_account_id(+)) AND (x.payment_channel_code = p.payment_channel_code) AND (c.card_issuer_code = i.card_issuer_code(+)) AND (c.card_owner_id = hzp.party_id(+)) AND (x.origin_application_id = a.application_id) AND (b.bank_id=bhzp.party_id(+)) AND (b.branch_id=brhzp.party_id(+)) and (bhzp.party_id=bankProfile.party_id(+)) and (I.card_issuer_code=IT.card_issuer_code(+)) and (IT.language(+)=USERENV('LANG')) and (P.payment_channel_code=PT.payment_channel_code) and (PT.language=USERENV('LANG')) AND (x.trxn_extension_id = authtxn.trxn_extension_id(+)) AND (x.trxn_extension_id = settletxn.trxn_extension_id(+)) AND (x.trxn_extension_id = returntxn.trxn_extension_id(+)) AND TRUNC(sysdate) BETWEEN bankprofile.effective_start_date(+) AND bankprofile.effective_end_date(+) AND bankprofile.effective_latest_change(+) = 'Y' AND Settletxn.rank(+) = 1

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.ext_payer_id ext_payer_id,

x.instr_assignment_id INSTR_ASSIGNMENT_ID,

NVL(u.instrument_type,p.instrument_type) INSTRUMENT_TYPE,

u.instrument_id INSTRUMENT_ID, b.currency_code CURRENCY_CODE,

c.masked_cc_number CARD_NUMBER,

c.cc_number_hash1 CARD_NUMBER_HASH1, c.cc_number_hash2 CARD_NUMBER_HASH2,

c.expirydate CARD_EXPIRYDATE, c.card_issuer_code CARD_ISSUER_CODE,

IT.card_issuer_name CARD_ISSUER_NAME, c.purchasecard_subtype PURCHASECARD_SUBTYPE,

NVL(c.chname,hzp.party_name) CARD_HOLDER_NAME,

b.masked_bank_account_num ACCOUNT_NUMBER,

bankProfile.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.payment_system_order_number PAYMENT_SYSTEM_ORDER_NUMBER,

x.additional_info ADDITIONAL_INFO, DECODE( authtxn.status, NULL,'N', 'Y' ) AUTHORIZED_FLAG,

DECODE( authtxn.status, 0,'AUTH_SUCCESS', 100,'AUTH_PENDING', null) AUTHORIZATION_STATUS,

authtxn.bepcode AUTH_PAYSYS_CODE,

authtxn.bepmessage AUTH_PAYSYS_MSG,

DECODE( settletxn.status, NULL,'N', 'Y' ) SETTLED_FLAG,

DECODE( settletxn.status, 0,'SETTLEMENT_SUCCESS', 100,'SETTLEMENT_PENDING', null) SETTLEMENT_STATUS,

settletxn.bepcode SETTLE_PAYSYS_CODE,

settletxn.bepmessage SETTLE_PAYSYS_MSG,

DECODE( returntxn.status, NULL,'N', 'Y' ) RETURNED_FLAG,

DECODE( returntxn.status, 0,'RETURN_SUCCESS', 100,'RETURN_PENDING', null) RETURN_STATUS,

returntxn.bepcode RETURN_PAYSYS_CODE,

returntxn.bepmessage RETURN_PAYSYS_MSG,

bhzp.party_name BANK_NAME, brhzp.party_name BANK_BRANCH_NAME,

x.debit_authorization_id DEBIT_AUTHORIZATION_ID,

x.debit_auth_last_trx_flag DEBIT_AUTH_LAST_TRX_FLAG

FROM IBY_CREDITCARD C, IBY_CREDITCARD_ISSUERS_B I, IBY_CREDITCARD_ISSUERS_TL IT, IBY_EXT_BANK_ACCOUNTS B, IBY_FNDCPT_PMT_CHNNLS_B P, IBY_FNDCPT_PMT_CHNNLS_TL PT, IBY_FNDCPT_TX_EXTENSIONS X, IBY_PMT_INSTR_USES_ALL U, HZ_PARTIES HZP, FND_APPLICATION A, HZ_PARTIES bhzp, HZ_PARTIES brhzp, hz_organization_profiles bankProfile,

( SELECT op.trxn_extension_id, summ.status, summ.bepcode, summ.bepmessage,

summ.last_update_date FROM iby_trxn_summaries_all summ, iby_fndcpt_tx_operations op WHERE summ.transactionid = op.transactionid AND reqtype = 'ORAPMTREQ' AND status IN (0,100) AND trxntypeid IN (2,3,20) AND summ.last_update_date = (SELECT MAX(int_summ.last_update_date) FROM iby_trxn_summaries_all int_summ WHERE reqtype = 'ORAPMTREQ' AND status IN (0,100) AND trxntypeid IN (2,3,20) AND int_summ.transactionid in ( select transactionid from iby_fndcpt_tx_operations op1 where op1.trxn_extension_id = op.trxn_extension_id ) ) ) authtxn,

(SELECT op.trxn_extension_id, summ.status,summ.bepcode,summ.bepmessage,

Rank() over (PARTITION BY Op.Trxn_Extension_Id ORDER BY op.Transactionid ) rank FROM iby_trxn_summaries_all summ, iby_fndcpt_tx_operations op WHERE summ.transactionid = op.transactionid AND ((instrtype IN ('CREDITCARD', 'PURCHASECARD') AND (reqtype = 'ORAPMTCAPTURE' OR trxntypeid = 3 ) ) OR (instrtype = 'BANKACCOUNT' AND reqtype = 'ORAPMTBATCHREQ') OR (instrtype = 'PINLESSDEBITCARD' AND reqtype = 'ORAPMTREQ')) AND status IN (0,100) ) settletxn,

(SELECT op.trxn_extension_id, summ.status,summ.bepcode,summ.bepmessage FROM iby_trxn_summaries_all summ,

iby_fndcpt_tx_operations op WHERE summ.transactionid = op.transactionid AND

((reqtype = 'ORAPMTRETURN') OR (reqtype = 'ORAPMTCREDIT')) AND status IN (0,100)

AND summ.last_update_date= ( SELECT MAX(summ1.last_update_date) FROM iby_trxn_summaries_all summ1 WHERE ((reqtype = 'ORAPMTRETURN') OR (reqtype = 'ORAPMTCREDIT')) AND status IN (0,100) AND summ1.transactionid in (select transactionid from iby_fndcpt_tx_operations op1 where op1.trxn_extension_id = op.trxn_extension_id ) ) ) returntxn

WHERE (x.instr_assignment_id = u.instrument_payment_use_id(+)) AND (DECODE(u.instrument_type, 'CREDITCARD',u.instrument_id, NULL) = c.instrid(+)) AND (DECODE(u.instrument_type, 'BANKACCOUNT',u.instrument_id, NULL) = b.ext_bank_account_id(+)) AND (x.payment_channel_code = p.payment_channel_code) AND (c.card_issuer_code = i.card_issuer_code(+)) AND (c.card_owner_id = hzp.party_id(+)) AND (x.origin_application_id = a.application_id) AND (b.bank_id=bhzp.party_id(+)) AND (b.branch_id=brhzp.party_id(+)) and (bhzp.party_id=bankProfile.party_id(+)) and (I.card_issuer_code=IT.card_issuer_code(+)) and (IT.language(+)=USERENV('LANG')) and (P.payment_channel_code=PT.payment_channel_code) and (PT.language=USERENV('LANG')) AND (x.trxn_extension_id = authtxn.trxn_extension_id(+)) AND (x.trxn_extension_id = settletxn.trxn_extension_id(+)) AND (x.trxn_extension_id = returntxn.trxn_extension_id(+))

AND TRUNC(sysdate) BETWEEN bankprofile.effective_start_date(+)

AND bankprofile.effective_end_date(+)

AND bankprofile.effective_latest_change(+) = 'Y'

AND Settletxn.rank(+) = 1