VIEW: APPS.OKX_RCPT_METHOD_ACCOUNTS_V

Source

View Text - Preformatted

SELECT IPIUA.INSTRUMENT_PAYMENT_USE_ID ID1, '#' ID2, IEBA.BANK_ACCOUNT_NAME||'-'||HCSU.LOCATION NAME, IEBA.MASKED_BANK_ACCOUNT_NUM DESCRIPTION, BNK.PARTY_NAME BANK_NAME, IEBA.EXT_BANK_ACCOUNT_ID BANK_ACCOUNT_ID, IEBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM, IEBA.BRANCH_ID BANK_BRANCH_ID, NULL SET_OF_BOOKS_ID, IEBA.CURRENCY_CODE CURRENCY_CODE, IPIUA.INSTRUMENT_TYPE ACCOUNT_TYPE, IEPA.CUST_ACCOUNT_ID CUSTOMER_ID, HCSU.SITE_USE_ID CUSTOMER_SITE_USE_ID, IPIUA.START_DATE START_DATE_ACTIVE, IPIUA.END_DATE END_DATE_ACTIVE, DECODE(SIGN(TRUNC(SYSDATE)-NVL(IEBA.START_DATE,TRUNC(SYSDATE))),-1,'I', DECODE(SIGN(TRUNC(SYSDATE) - NVL(IEBA.END_DATE,TRUNC(SYSDATE))), 1,'I','A')) STATUS, HCSU.ORG_ID ORG_ID, NULL MULTI_CURRENCY_FLAG, NULL RECEIPT_MULTI_CURRENCY_FLAG, NULL CONTACT_FIRST_NAME, NULL CONTACT_MIDDLE_NAME, NULL CONTACT_LAST_NAME, NULL CONTACT_PREFIX, NULL CONTACT_TITLE, NULL CONTACT_AREA_CODE, NULL CONTACT_PHONE, BNK.PARTY_NAME BANK, IEBA.BANK_ACCOUNT_NAME HOLDER_NAME, NULL EXPIRATION_STATUS FROM HZ_CUST_SITE_USES_ALL HCSU,IBY_EXT_BANK_ACCOUNTS IEBA,IBY_PMT_INSTR_USES_ALL IPIUA,IBY_EXTERNAL_PAYERS_ALL IEPA,HZ_PARTIES BNK WHERE IEBA.BANK_ID = BNK.PARTY_ID AND HCSU.SITE_USE_ID(+) = IEPA.ACCT_SITE_USE_ID AND IPIUA.INSTRUMENT_ID = IEBA.EXT_BANK_ACCOUNT_ID AND IPIUA.EXT_PMT_PARTY_ID = IEPA.EXT_PAYER_ID AND IPIUA.PAYMENT_FLOW = 'FUNDS_CAPTURE' UNION SELECT IPIUA.INSTRUMENT_PAYMENT_USE_ID ID1, '#' ID2, ICC.CCNUMBER||'-'||HCSU.LOCATION NAME, ICC.MASKED_CC_NUMBER DESCRIPTION, ICC.INSTRNAME BANK_NAME, ICC.INSTRID BANK_ACCOUNT_ID, ICC.CCNUMBER BANK_ACCOUNT_NUM, 1 BANK_BRANCH_ID, NULL SET_OF_BOOKS_ID, TO_CHAR(NULL) CURRENCY_CODE, IPIUA.INSTRUMENT_TYPE ACCOUNT_TYPE, IEPA.CUST_ACCOUNT_ID CUSTOMER_ID, HCSU.SITE_USE_ID CUSTOMER_SITE_USE_ID, IPIUA.START_DATE START_DATE_ACTIVE, IPIUA.END_DATE END_DATE_ACTIVE, DECODE(SIGN(TRUNC(SYSDATE) - NVL(ICC.EXPIRYDATE,TRUNC(SYSDATE))), 1,'I','A') STATUS, HCSU.ORG_ID ORG_ID, NULL MULTI_CURRENCY_FLAG, NULL RECEIPT_MULTI_CURRENCY_FLAG, NULL CONTACT_FIRST_NAME, NULL CONTACT_MIDDLE_NAME, NULL CONTACT_LAST_NAME, NULL CONTACT_PREFIX, NULL CONTACT_TITLE, NULL CONTACT_AREA_CODE, NULL CONTACT_PHONE, ICI.CARD_ISSUER_NAME BANK, ICC.CHNAME HOLDER_NAME, lkp.meaning EXPIRATION_STATUS FROM IBY_CREDITCARD ICC , IBY_PMT_INSTR_USES_ALL IPIUA , IBY_EXTERNAL_PAYERS_ALL IEPA , HZ_CUST_SITE_USES_ALL HCSU , IBY_CREDITCARD_ISSUERS_VL ICI , FND_LOOKUPS lkp WHERE HCSU.SITE_USE_ID(+) = IEPA.ACCT_SITE_USE_ID AND IPIUA.INSTRUMENT_ID = ICC.INSTRID AND IPIUA.EXT_PMT_PARTY_ID = IEPA.EXT_PAYER_ID AND IPIUA.PAYMENT_FLOW = 'FUNDS_CAPTURE' AND ICC.CARD_ISSUER_CODE = ICI.CARD_ISSUER_CODE (+) AND lkp.lookup_type = 'IBY_CARD_EXPIRATION_STATUS' AND lkp.lookup_code = decode(DECODE(ICC.expirydate, null, ICC.expired_flag, decode(sign(ICC.expirydate-sysdate),-1,'Y', 'N')),'Y', 'EXPIRED','N','UNEXPIRED')

View Text - HTML Formatted

SELECT IPIUA.INSTRUMENT_PAYMENT_USE_ID ID1
, '#' ID2
, IEBA.BANK_ACCOUNT_NAME||'-'||HCSU.LOCATION NAME
, IEBA.MASKED_BANK_ACCOUNT_NUM DESCRIPTION
, BNK.PARTY_NAME BANK_NAME
, IEBA.EXT_BANK_ACCOUNT_ID BANK_ACCOUNT_ID
, IEBA.BANK_ACCOUNT_NUM BANK_ACCOUNT_NUM
, IEBA.BRANCH_ID BANK_BRANCH_ID
, NULL SET_OF_BOOKS_ID
, IEBA.CURRENCY_CODE CURRENCY_CODE
, IPIUA.INSTRUMENT_TYPE ACCOUNT_TYPE
, IEPA.CUST_ACCOUNT_ID CUSTOMER_ID
, HCSU.SITE_USE_ID CUSTOMER_SITE_USE_ID
, IPIUA.START_DATE START_DATE_ACTIVE
, IPIUA.END_DATE END_DATE_ACTIVE
, DECODE(SIGN(TRUNC(SYSDATE)-NVL(IEBA.START_DATE
, TRUNC(SYSDATE)))
, -1
, 'I'
, DECODE(SIGN(TRUNC(SYSDATE) - NVL(IEBA.END_DATE
, TRUNC(SYSDATE)))
, 1
, 'I'
, 'A')) STATUS
, HCSU.ORG_ID ORG_ID
, NULL MULTI_CURRENCY_FLAG
, NULL RECEIPT_MULTI_CURRENCY_FLAG
, NULL CONTACT_FIRST_NAME
, NULL CONTACT_MIDDLE_NAME
, NULL CONTACT_LAST_NAME
, NULL CONTACT_PREFIX
, NULL CONTACT_TITLE
, NULL CONTACT_AREA_CODE
, NULL CONTACT_PHONE
, BNK.PARTY_NAME BANK
, IEBA.BANK_ACCOUNT_NAME HOLDER_NAME
, NULL EXPIRATION_STATUS
FROM HZ_CUST_SITE_USES_ALL HCSU
, IBY_EXT_BANK_ACCOUNTS IEBA
, IBY_PMT_INSTR_USES_ALL IPIUA
, IBY_EXTERNAL_PAYERS_ALL IEPA
, HZ_PARTIES BNK
WHERE IEBA.BANK_ID = BNK.PARTY_ID
AND HCSU.SITE_USE_ID(+) = IEPA.ACCT_SITE_USE_ID
AND IPIUA.INSTRUMENT_ID = IEBA.EXT_BANK_ACCOUNT_ID
AND IPIUA.EXT_PMT_PARTY_ID = IEPA.EXT_PAYER_ID
AND IPIUA.PAYMENT_FLOW = 'FUNDS_CAPTURE' UNION SELECT IPIUA.INSTRUMENT_PAYMENT_USE_ID ID1
, '#' ID2
, ICC.CCNUMBER||'-'||HCSU.LOCATION NAME
, ICC.MASKED_CC_NUMBER DESCRIPTION
, ICC.INSTRNAME BANK_NAME
, ICC.INSTRID BANK_ACCOUNT_ID
, ICC.CCNUMBER BANK_ACCOUNT_NUM
, 1 BANK_BRANCH_ID
, NULL SET_OF_BOOKS_ID
, TO_CHAR(NULL) CURRENCY_CODE
, IPIUA.INSTRUMENT_TYPE ACCOUNT_TYPE
, IEPA.CUST_ACCOUNT_ID CUSTOMER_ID
, HCSU.SITE_USE_ID CUSTOMER_SITE_USE_ID
, IPIUA.START_DATE START_DATE_ACTIVE
, IPIUA.END_DATE END_DATE_ACTIVE
, DECODE(SIGN(TRUNC(SYSDATE) - NVL(ICC.EXPIRYDATE
, TRUNC(SYSDATE)))
, 1
, 'I'
, 'A') STATUS
, HCSU.ORG_ID ORG_ID
, NULL MULTI_CURRENCY_FLAG
, NULL RECEIPT_MULTI_CURRENCY_FLAG
, NULL CONTACT_FIRST_NAME
, NULL CONTACT_MIDDLE_NAME
, NULL CONTACT_LAST_NAME
, NULL CONTACT_PREFIX
, NULL CONTACT_TITLE
, NULL CONTACT_AREA_CODE
, NULL CONTACT_PHONE
, ICI.CARD_ISSUER_NAME BANK
, ICC.CHNAME HOLDER_NAME
, LKP.MEANING EXPIRATION_STATUS
FROM IBY_CREDITCARD ICC
, IBY_PMT_INSTR_USES_ALL IPIUA
, IBY_EXTERNAL_PAYERS_ALL IEPA
, HZ_CUST_SITE_USES_ALL HCSU
, IBY_CREDITCARD_ISSUERS_VL ICI
, FND_LOOKUPS LKP
WHERE HCSU.SITE_USE_ID(+) = IEPA.ACCT_SITE_USE_ID
AND IPIUA.INSTRUMENT_ID = ICC.INSTRID
AND IPIUA.EXT_PMT_PARTY_ID = IEPA.EXT_PAYER_ID
AND IPIUA.PAYMENT_FLOW = 'FUNDS_CAPTURE'
AND ICC.CARD_ISSUER_CODE = ICI.CARD_ISSUER_CODE (+)
AND LKP.LOOKUP_TYPE = 'IBY_CARD_EXPIRATION_STATUS'
AND LKP.LOOKUP_CODE = DECODE(DECODE(ICC.EXPIRYDATE
, NULL
, ICC.EXPIRED_FLAG
, DECODE(SIGN(ICC.EXPIRYDATE-SYSDATE)
, -1
, 'Y'
, 'N'))
, 'Y'
, 'EXPIRED'
, 'N'
, 'UNEXPIRED')