VIEW: APPS.IBY_FNDCPT_PAYER_ALL_INSTRS_V

Source

View Text - Preformatted

SELECT c.card_owner_id, 'CREDITCARD', c.instrid, NULL, 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, i.card_issuer_name, c.purchasecard_subtype, c.chname, c.addressid, c.finame, c.single_use_flag, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, c.description, nvl(c.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 iby_creditcard c, iby_creditcard_issuers_vl i WHERE(c.card_issuer_code = i.card_issuer_code) UNION SELECT o.account_owner_party_id, 'BANKACCOUNT', b.bank_account_id, b.currency_code, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, b.bank_account_number, b.bank_acct_num_hash1, b.bank_acct_num_hash2, b.bank_name, b.bank_number, b.bank_branch_name, b.branch_number, NULL, b.description, nvl(b.encrypted,'N'), NULL FROM iby_account_owners o, iby_ext_bank_accounts_v b WHERE(o.ext_bank_account_id(+) = b.bank_account_id)

View Text - HTML Formatted

SELECT C.CARD_OWNER_ID
, 'CREDITCARD'
, C.INSTRID
, NULL
, 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
, I.CARD_ISSUER_NAME
, C.PURCHASECARD_SUBTYPE
, C.CHNAME
, C.ADDRESSID
, C.FINAME
, C.SINGLE_USE_FLAG
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, C.DESCRIPTION
, NVL(C.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 IBY_CREDITCARD C
, IBY_CREDITCARD_ISSUERS_VL I WHERE(C.CARD_ISSUER_CODE = I.CARD_ISSUER_CODE) UNION SELECT O.ACCOUNT_OWNER_PARTY_ID
, 'BANKACCOUNT'
, B.BANK_ACCOUNT_ID
, B.CURRENCY_CODE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, B.BANK_ACCOUNT_NUMBER
, B.BANK_ACCT_NUM_HASH1
, B.BANK_ACCT_NUM_HASH2
, B.BANK_NAME
, B.BANK_NUMBER
, B.BANK_BRANCH_NAME
, B.BRANCH_NUMBER
, NULL
, B.DESCRIPTION
, NVL(B.ENCRYPTED
, 'N')
, NULL
FROM IBY_ACCOUNT_OWNERS O
, IBY_EXT_BANK_ACCOUNTS_V B WHERE(O.EXT_BANK_ACCOUNT_ID(+) = B.BANK_ACCOUNT_ID)