IBY_FNDCPT_PAYER_ASSGN_INSTR

Details

Schema: FUSION
Object owner: IBY
Object type: VIEW

Columns

Name
ROW_ID INSTR_ASSIGNMENT_ID EXT_PAYER_ID PAYMENT_FUNCTION PARTY_ID ORG_TYPE ORG_ID CUST_ACCOUNT_ID ACCT_SITE_USE_ID INSTRUMENT_TYPE INSTRUMENT_ID PRIMARY_FLAG CURRENCY_CODE CARD_NUMBER CC_NUMBER_HASH1 CC_NUMBER_HASH2 CARD_EXPIRYDATE CARD_ISSUER_CODE BEPID BEP_ACCOUNT_ID USER_PROFILE_CODE CARD_ISSUER_NAME CARD_ISSUER_ACCEPTED_FLAG PURCHASECARD_SUBTYPE CARD_HOLDER_NAME CARD_BILLING_ADDRESS_ID CARD_FI_NAME CARD_SINGLE_USE_FLAG CARD_INFO_ONLY_FLAG ACCOUNT_NUMBER ACCOUNT_COUNTRY BANK_NAME BANK_NUMBER BANK_ACCT_NUM_HASH1 BANK_ACCT_NUM_HASH2 BRANCH_NAME BRANCH_NUMBER BIC_NUMBER DESCRIPTION ASSIGNMENT_LAST_UPDATE ASSIGNMENT_START_DATE ASSIGNMENT_END_DATE CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN OBJECT_VERSION_NUMBER

Name

ROW_ID

INSTR_ASSIGNMENT_ID

EXT_PAYER_ID

PAYMENT_FUNCTION

PARTY_ID

ORG_TYPE

ORG_ID

CUST_ACCOUNT_ID

ACCT_SITE_USE_ID

INSTRUMENT_TYPE

INSTRUMENT_ID

PRIMARY_FLAG

CURRENCY_CODE

CARD_NUMBER

CC_NUMBER_HASH1

CC_NUMBER_HASH2

CARD_EXPIRYDATE

CARD_ISSUER_CODE

BEPID

BEP_ACCOUNT_ID

USER_PROFILE_CODE

CARD_ISSUER_NAME

CARD_ISSUER_ACCEPTED_FLAG

PURCHASECARD_SUBTYPE

CARD_HOLDER_NAME

CARD_BILLING_ADDRESS_ID

CARD_FI_NAME

CARD_SINGLE_USE_FLAG

CARD_INFO_ONLY_FLAG

ACCOUNT_NUMBER

ACCOUNT_COUNTRY

BANK_NAME

BANK_NUMBER

BANK_ACCT_NUM_HASH1

BANK_ACCT_NUM_HASH2

BRANCH_NAME

BRANCH_NUMBER

BIC_NUMBER

DESCRIPTION

ASSIGNMENT_LAST_UPDATE

ASSIGNMENT_START_DATE

ASSIGNMENT_END_DATE

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

Query

SQL_Statement
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.primary_flag, b.currency_code, c.masked_cc_number, c.cc_number_hash1, c.cc_number_hash2, c.expirydate, c.card_issuer_code, c.bepid, c.bep_account_id, c.user_profile_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.masked_bank_account_num bank_account_number, b.country_code account_country, bp.party_name bank_name, bapr.bank_or_branch_number bank_number, b.bank_account_num_hash1 bank_acct_num_hash1, b.bank_account_num_hash2 bank_acct_num_hash2, br.party_name bank_branch_name, brpr.bank_or_branch_number branch_number, BranchCP.eft_swift_code, DECODE(u.instrument_type, 'BANKACCOUNT',b.description, c.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 FROM FND_LOOKUP_VALUES_VL CCUNK, IBY_CREDITCARD C, IBY_CREDITCARD_ISSUERS_VL I, IBY_EXTERNAL_PAYERS_ALL P, IBY_EXT_BANK_ACCOUNTS B, HZ_ORGANIZATION_PROFILES BAPR, HZ_ORGANIZATION_PROFILES BrPR, HZ_PARTIES BP, HZ_PARTIES BR, HZ_CONTACT_POINTS BranchCP, 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.ext_bank_account_id(+)) AND b.bank_id = bp.party_id(+) AND b.bank_id = bapr.party_id(+) AND b.branch_id = br.party_id(+) AND b.branch_id = brpr.party_id(+) AND (BranchCP.owner_table_name(+) = 'HZ_PARTIES') AND (BranchCP.owner_table_id(+) = b.branch_id) AND (BranchCP.contact_point_type(+) = 'EFT' ) AND (NVL(BranchCP.status(+), 'A') = 'A') AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(Bapr.effective_start_date), sysdate-1) AND NVL(TRUNC(Bapr.effective_end_date), SYSDATE+1) AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(Brpr.effective_start_date) ,sysdate-1) AND NVL(TRUNC(Brpr.effective_end_date), SYSDATE+1) 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.end_date,SYSDATE+10) > SYSDATE) AND (ccunk.lookup_type ='IBY_CARD_TYPES') AND (ccunk.lookup_code ='UNKNOWN')

SQL_Statement

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.primary_flag,

b.currency_code,

c.masked_cc_number,

c.cc_number_hash1,

c.cc_number_hash2,

c.expirydate,

c.card_issuer_code,

c.bepid,

c.bep_account_id,

c.user_profile_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.masked_bank_account_num bank_account_number,

b.country_code account_country,

bp.party_name bank_name,

bapr.bank_or_branch_number bank_number,

b.bank_account_num_hash1 bank_acct_num_hash1,

b.bank_account_num_hash2 bank_acct_num_hash2,

br.party_name bank_branch_name,

brpr.bank_or_branch_number branch_number,

BranchCP.eft_swift_code,

DECODE(u.instrument_type, 'BANKACCOUNT',b.description, c.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

FROM FND_LOOKUP_VALUES_VL CCUNK,

IBY_CREDITCARD C,

IBY_CREDITCARD_ISSUERS_VL I,

IBY_EXTERNAL_PAYERS_ALL P,

IBY_EXT_BANK_ACCOUNTS B,

HZ_ORGANIZATION_PROFILES BAPR,

HZ_ORGANIZATION_PROFILES BrPR,

HZ_PARTIES BP,

HZ_PARTIES BR,

HZ_CONTACT_POINTS BranchCP,

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.ext_bank_account_id(+))

AND b.bank_id = bp.party_id(+)

AND b.bank_id = bapr.party_id(+)

AND b.branch_id = br.party_id(+)

AND b.branch_id = brpr.party_id(+)

AND (BranchCP.owner_table_name(+) = 'HZ_PARTIES')

AND (BranchCP.owner_table_id(+) = b.branch_id)

AND (BranchCP.contact_point_type(+) = 'EFT' )

AND (NVL(BranchCP.status(+), 'A') = 'A')

AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(Bapr.effective_start_date), sysdate-1) AND NVL(TRUNC(Bapr.effective_end_date), SYSDATE+1)

AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(Brpr.effective_start_date) ,sysdate-1) AND NVL(TRUNC(Brpr.effective_end_date), SYSDATE+1)

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.end_date,SYSDATE+10) > SYSDATE)

AND (ccunk.lookup_type ='IBY_CARD_TYPES')

AND (ccunk.lookup_code ='UNKNOWN')