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, IEBA.START_DATE START_DATE_ACTIVE, IEBA.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 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, TO_DATE(NULL) START_DATE_ACTIVE, ICC.EXPIRYDATE 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 FROM IBY_CREDITCARD ICC , IBY_PMT_INSTR_USES_ALL IPIUA , IBY_EXTERNAL_PAYERS_ALL IEPA , HZ_CUST_SITE_USES_ALL HCSU 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'

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
, IEBA.START_DATE START_DATE_ACTIVE
, IEBA.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
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
, TO_DATE(NULL) START_DATE_ACTIVE
, ICC.EXPIRYDATE 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
FROM IBY_CREDITCARD ICC
, IBY_PMT_INSTR_USES_ALL IPIUA
, IBY_EXTERNAL_PAYERS_ALL IEPA
, HZ_CUST_SITE_USES_ALL HCSU
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'