Results for “iby_trxn_core”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

IBY_TRXN_CORE is a core payment-instrument transaction table owned by the IBY (Payments) schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It stores the low-level transaction detail captured during payment processing — authorization results, terminal and trace identifiers, point-of-sale data, cardholder or instrument-owner attributes, and security-segment references. The table carries 50 documented columns and is validated in the ETRM 12.2.2 release.

From a Data Vault modeling perspective, the mined FK structure classifies IBY_TRXN_CORE as satellite-leaning. This is a heuristic suggestion rather than a database constraint: the object holds descriptive, transaction-scoped attributes that cluster around a primary transactional hub (IBY_TRXN_SUMMARIES_ALL), making it a natural candidate for a satellite table in a Data Vault design rather than a standalone hub or link.

Key Information Stored

The surrogate primary key is TRXNMID, enforced by the constraint IBY_TRXN_CORE_PK and backed by the unique index IBY_TRXN_CORE_TRXNMID_U1. TRXNMID is also the sole business-key candidate identified in the documentation, and it doubles as the foreign key joining this table to IBY_TRXN_SUMMARIES_ALL. The most significant descriptive columns include:

• TRXNMID — Primary key and join key to transaction summaries.
• TERMINALID, TRACENUMBER, PROC_TRACENUMBER — Terminal and network trace identifiers used to reconcile a transaction across the payment network.
• AUTHCODE, REFERENCECODE, AUTHTYPE, OPERATIONCODE — Authorization outcome codes and operation classification.
• AVSCODE, CVV2RESULT, VOICEAUTHFLAG, DATE_OF_VOICE_AUTHORIZATION — Fraud and verification responses, including address verification, card verification value results, and voice-authorization audit data.
• POS_READER_CAPABILITY_CODE, POS_ENTRY_METHOD_CODE, POS_ID_METHOD_CODE, POS_AUTH_SOURCE_CODE, POS_TRXN_FLAG, READER_DATA — Point-of-sale capture characteristics that describe how the instrument data was presented.
• INSTR_NAME, INSTR_EXPIRYDATE, CARD_SUBTYPE_CODE, CARD_DATA_LEVEL, DEBIT_NETWORK_CODE — Instrument identity and classification fields.
• INSTR_OWNER_NAME, INSTR_OWNER_ADDRESS_LINE1–3, INSTR_OWNER_CITY, INSTR_OWNER_STATE_PROVINCE, INSTR_OWNER_COUNTRY, INSTR_OWNER_POSTALCODE, INSTR_OWNER_PHONE, INSTR_OWNER_EMAIL — Cardholder or instrument-owner contact and billing data.
• PONUMBER, TAXAMOUNT, SHIPFROMZIP, SHIPTOZIP, SURCHARGE_AMOUNT — Purchase-order reference and monetary context.
• ENCRYPTED, EXPIRY_SEC_SEGMENT_ID, CHNAME_SEC_SEGMENT_ID, SECURITY_GROUP_ID — Encryption flag, security-segment pointers, and the multi-org security-group identifier.
• OBJECT_VERSION_NUMBER, LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATED_BY, CREATION_DATE, LAST_UPDATE_LOGIN — Standard EBS WHO-column audit set.

Common Use Cases and Queries

The table is most often queried for payment authorization reporting, fraud/AVS review, and POS reconciliation. A typical join pattern resolves transaction-level detail to its summary record:

• Reconcile authorizations by trace number: SELECT s.TRXNMID, s.TOTAL, c.AUTHCODE, c.TRACENUMBER FROM IBY_TRXN_SUMMARIES_ALL s, IBY_TRXN_CORE c WHERE s.TRXNMID = c.TRXNMID.
• AVS/CVV review reporting: SELECT TRXNMID, AVSCODE, CVV2RESULT FROM IBY_TRXN_CORE WHERE AVSCODE NOT IN ('Y','M').
• POS analytics grouping by POS_ENTRY_METHOD_CODE or POS_READER_CAPABILITY_CODE to assess capture channels.
• Security-scoped extraction using SECURITY_GROUP_ID to honor multi-org data access rules.

Because the table carries owner PII and card data, queries should observe IBY payment-security policies and any data-masking views layered over it.

Related Objects

The following objects are most directly related, based on documented FK/PK relationships:

• IBY_TRXN_SUMMARIES_ALL — Parent summary table joined via IBY_TRXN_CORE.TRXNMID.
• IBY_SECURITY_SEGMENTS — Referenced twice, via CHNAME_SEC_SEGMENT_ID and EXPIRY_SEC_SEGMENT_ID, for encrypted cardholder-name and expiry tokenization.
• FND_SECURITY_GROUPS — Referenced via SECURITY_GROUP_ID for multi-org security enforcement.
• IBY_TRXN_CORE_PK / IBY_TRXN_CORE_TRXNMID_U1 — Primary-key constraint and unique index on TRXNMID.

Together these objects form the transactional spine through which IBY payment instrument data is stored, secured, and reported in EBS 12.1.1 and 12.2.2.