VIEW: APPS.IBY_FNDCPT_PAYER_ASSGN_INSTR_V

Source

View Text - Preformatted

SELECT u.rowid, u.instrument_payment_use_id, u.ext_pmt_party_id, p.payment_function, p.party_id, p.org_type, p.org_id, p.cust_account_id, p.acct_site_use_id, u.instrument_type, u.instrument_id, u.order_of_preference, b.currency_code, c.masked_cc_number, c.cc_number_hash1, c.cc_number_hash2, decode(c.encrypted,'A',null,c.expirydate), decode(c.encrypted,'A','XX/XX',to_char(c.expirydate, 'mm/yy')), DECODE(c.expirydate, null, c.expired_flag, decode(sign(c.expirydate-sysdate),-1,'Y','N')), c.card_issuer_code, decode(i.card_issuer_code, NULL, ccunk.meaning, i.card_issuer_name), i.card_issuer_accepted_flag, c.purchasecard_subtype, nvl(c.chname,hzcc.party_name), c.addressid, c.finame, c.single_use_flag, c.information_only_flag, b.bank_account_number, b.bank_name, b.bank_number, b.bank_acct_num_hash1, b.bank_acct_num_hash2, b.bank_branch_name, b.branch_number, NULL, nvl(c.description, b.description), u.last_update_date, u.start_date, u.end_date, u.created_by, u.creation_date, u.last_updated_by, u.last_update_date, u.last_update_login, u.object_version_number, DECODE(u.instrument_type,'CREDITCARD', NVL(c.encrypted, 'N'), NVL(b.encrypted, 'N')), (select lkp.meaning from fnd_lookups lkp where lkp.lookup_type = 'IBY_CARD_EXPIRATION_STATUS' and (decode(DECODE(c.expirydate, null, c.expired_flag, decode(sign(c.expirydate-sysdate),-1,'Y','N')),'Y', 'EXPIRED','N','UNEXPIRED')) = lkp.lookup_code) FROM FND_LOOKUP_VALUES_VL CCUNK,IBY_CREDITCARD C,IBY_CREDITCARD_ISSUERS_VL I,IBY_EXTERNAL_PAYERS_ALL P,IBY_EXT_BANK_ACCOUNTS_V B,IBY_PMT_INSTR_USES_ALL U,HZ_PARTIES HZCC WHERE (u.ext_pmt_party_id = p.ext_payer_id) AND (DECODE(u.instrument_type, 'CREDITCARD',u.instrument_id, 'DEBITCARD',u.instrument_id, NULL) = c.instrid(+)) AND (DECODE(u.instrument_type, 'BANKACCOUNT',u.instrument_id, NULL) = b.bank_account_id(+)) AND (c.card_issuer_code = i.card_issuer_code(+)) AND (c.card_owner_id = hzcc.party_id(+)) AND (u.payment_flow = 'FUNDS_CAPTURE') AND (NVL(c.inactive_date,SYSDATE+10) > SYSDATE) AND (NVL(c.active_flag, 'Y') = 'Y') AND (ccunk.lookup_type='IBY_CARD_TYPES') AND (ccunk.lookup_code='UNKNOWN')

View Text - HTML Formatted

SELECT U.ROWID
, U.INSTRUMENT_PAYMENT_USE_ID
, U.EXT_PMT_PARTY_ID
, P.PAYMENT_FUNCTION
, P.PARTY_ID
, P.ORG_TYPE
, P.ORG_ID
, P.CUST_ACCOUNT_ID
, P.ACCT_SITE_USE_ID
, U.INSTRUMENT_TYPE
, U.INSTRUMENT_ID
, U.ORDER_OF_PREFERENCE
, B.CURRENCY_CODE
, C.MASKED_CC_NUMBER
, C.CC_NUMBER_HASH1
, C.CC_NUMBER_HASH2
, DECODE(C.ENCRYPTED
, 'A'
, NULL
, C.EXPIRYDATE)
, DECODE(C.ENCRYPTED
, 'A'
, 'XX/XX'
, TO_CHAR(C.EXPIRYDATE
, 'MM/YY'))
, DECODE(C.EXPIRYDATE
, NULL
, C.EXPIRED_FLAG
, DECODE(SIGN(C.EXPIRYDATE-SYSDATE)
, -1
, 'Y'
, 'N'))
, C.CARD_ISSUER_CODE
, DECODE(I.CARD_ISSUER_CODE
, NULL
, CCUNK.MEANING
, I.CARD_ISSUER_NAME)
, I.CARD_ISSUER_ACCEPTED_FLAG
, C.PURCHASECARD_SUBTYPE
, NVL(C.CHNAME
, HZCC.PARTY_NAME)
, C.ADDRESSID
, C.FINAME
, C.SINGLE_USE_FLAG
, C.INFORMATION_ONLY_FLAG
, B.BANK_ACCOUNT_NUMBER
, B.BANK_NAME
, B.BANK_NUMBER
, B.BANK_ACCT_NUM_HASH1
, B.BANK_ACCT_NUM_HASH2
, B.BANK_BRANCH_NAME
, B.BRANCH_NUMBER
, NULL
, NVL(C.DESCRIPTION
, B.DESCRIPTION)
, U.LAST_UPDATE_DATE
, U.START_DATE
, U.END_DATE
, U.CREATED_BY
, U.CREATION_DATE
, U.LAST_UPDATED_BY
, U.LAST_UPDATE_DATE
, U.LAST_UPDATE_LOGIN
, U.OBJECT_VERSION_NUMBER
, DECODE(U.INSTRUMENT_TYPE
, 'CREDITCARD'
, NVL(C.ENCRYPTED
, 'N')
, NVL(B.ENCRYPTED
, 'N'))
, (SELECT LKP.MEANING
FROM FND_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = 'IBY_CARD_EXPIRATION_STATUS'
AND (DECODE(DECODE(C.EXPIRYDATE
, NULL
, C.EXPIRED_FLAG
, DECODE(SIGN(C.EXPIRYDATE-SYSDATE)
, -1
, 'Y'
, 'N'))
, 'Y'
, 'EXPIRED'
, 'N'
, 'UNEXPIRED')) = LKP.LOOKUP_CODE)
FROM FND_LOOKUP_VALUES_VL CCUNK
, IBY_CREDITCARD C
, IBY_CREDITCARD_ISSUERS_VL I
, IBY_EXTERNAL_PAYERS_ALL P
, IBY_EXT_BANK_ACCOUNTS_V B
, IBY_PMT_INSTR_USES_ALL U
, HZ_PARTIES HZCC
WHERE (U.EXT_PMT_PARTY_ID = P.EXT_PAYER_ID)
AND (DECODE(U.INSTRUMENT_TYPE
, 'CREDITCARD'
, U.INSTRUMENT_ID
, 'DEBITCARD'
, U.INSTRUMENT_ID
, NULL) = C.INSTRID(+))
AND (DECODE(U.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, U.INSTRUMENT_ID
, NULL) = B.BANK_ACCOUNT_ID(+))
AND (C.CARD_ISSUER_CODE = I.CARD_ISSUER_CODE(+))
AND (C.CARD_OWNER_ID = HZCC.PARTY_ID(+))
AND (U.PAYMENT_FLOW = 'FUNDS_CAPTURE')
AND (NVL(C.INACTIVE_DATE
, SYSDATE+10) > SYSDATE)
AND (NVL(C.ACTIVE_FLAG
, 'Y') = 'Y')
AND (CCUNK.LOOKUP_TYPE='IBY_CARD_TYPES')
AND (CCUNK.LOOKUP_CODE='UNKNOWN')