View: IBY_TRXN_EXTENSIONS_V

Product:	IBY - Payments
Description:
Implementation/DBA Data:	APPS.IBY_TRXN_EXTENSIONS_V

View Text

SELECT X.ROWID
, X.TRXN_EXTENSION_ID
, P.PAYMENT_CHANNEL_CODE
, P.PAYMENT_CHANNEL_NAME
, X.INSTR_ASSIGNMENT_ID
, NVL(U.INSTRUMENT_TYPE
, P.INSTRUMENT_TYPE)
, U.INSTRUMENT_ID
, B.CURRENCY_CODE
, C.MASKED_CC_NUMBER
, C.CC_NUMBER_HASH1
, C.CC_NUMBER_HASH2
, C.EXPIRYDATE
, C.CARD_ISSUER_CODE
, I.CARD_ISSUER_NAME
, C.PURCHASECARD_SUBTYPE
, NVL(C.CHNAME
, HZP.PARTY_NAME)
, B.BANK_ACCOUNT_NUMBER
, B.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')
, X.VOICE_AUTHORIZATION_FLAG
, X.VOICE_AUTHORIZATION_DATE
, X.VOICE_AUTHORIZATION_CODE
, X.ORIGIN_APPLICATION_ID
, X.ORDER_ID
, X.PO_NUMBER
, X.PO_LINE_NUMBER
, X.TRXN_REF_NUMBER1
, X.TRXN_REF_NUMBER2
, SUBSTR(IBY_FNDCPT_TRXN_PUB.GET_TANGIBLE_ID(A.APPLICATION_SHORT_NAME
, X.ORDER_ID
, X.TRXN_REF_NUMBER1
, X.TRXN_REF_NUMBER2)
, 1
, 80)
, X.ADDITIONAL_INFO
, DECODE( ( SELECT MAX(TRXNMID) AS MID
FROM IBY_TRXN_SUMMARIES_ALL
WHERE TRANSACTIONID = OP.TRANSACTIONID
AND REQTYPE = 'ORAPMTREQ'
AND STATUS IN (0
, 100)
AND TRXNTYPEID IN (2
, 3
, 20) )
, NULL
, 'N'
, 'Y' )
, DECODE( ( SELECT MAX(TRXNMID) AS MID
FROM IBY_TRXN_SUMMARIES_ALL
WHERE TRANSACTIONID = OP.TRANSACTIONID
AND ( (INSTRTYPE = 'CREDITCARD'
AND REQTYPE = 'ORAPMTCAPTURE') OR (INSTRTYPE = 'PINLESSDEBITCARD'
AND REQTYPE = 'ORAPMTREQ') OR (INSTRTYPE = 'BANKACCOUNT'
AND TRXNTYPEID IS NULL) )
AND STATUS IN (0
, 100) )
, NULL
, 'N'
, 'Y' )
, DECODE( ( SELECT MAX(TRXNMID) AS MID
FROM IBY_TRXN_SUMMARIES_ALL
WHERE TRANSACTIONID = OP.TRANSACTIONID
AND ( (REQTYPE = 'ORAPMTCREDIT') OR (REQTYPE = 'ORAPMTRETURN') )
AND STATUS IN (0
, 100) )
, NULL
, 'N'
, 'Y' )
, B.BANK_NAME
, B.BANK_BRANCH_NAME
FROM IBY_CREDITCARD C
, IBY_CREDITCARD_ISSUERS_VL I
, IBY_EXT_BANK_ACCOUNTS_V B
, IBY_FNDCPT_PMT_CHNNLS_VL P
, IBY_FNDCPT_TX_EXTENSIONS X
, IBY_FNDCPT_TX_OPERATIONS OP
, IBY_PMT_INSTR_USES_ALL U
, HZ_PARTIES HZP
, FND_APPLICATION A
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.BANK_ACCOUNT_ID(+))
AND (X.PAYMENT_CHANNEL_CODE = P.PAYMENT_CHANNEL_CODE)
AND (C.CARD_ISSUER_CODE = I.CARD_ISSUER_CODE(+))
AND (X.TRXN_EXTENSION_ID = OP.TRXN_EXTENSION_ID(+))
AND (C.CARD_OWNER_ID = HZP.PARTY_ID(+))
AND (X.ORIGIN_APPLICATION_ID = A.APPLICATION_ID)

Columns