Search Results sys_il0000510751c00028

Overview

IBY.IBY_DEBIT_AUTHORIZATIONS is a transactional table in the Oracle Payments (IBY) schema that stores mandate, or debit authorization, information for bank accounts used within Oracle E-Business Suite Release 12.1.1 and 12.2.2. A debit authorization represents the customer's standing permission for a creditor to collect funds directly from a designated bank account, together with the governing terms such as signing date, cancellation date, method, and validity window. In EBS, the table underpins direct debit and direct debit-related payment processing, where a valid mandate must exist before a payment instruction can be transmitted to a payment system or bank.

The table resides in the APPS_TS_TX_DATA tablespace and is documented with 28 columns and a primary key, IBY_DEBIT_AUTHORIZATIONS_PK, defined on DEBIT_AUTHORIZATION_ID. The ETRM metadata classifies the object as standalone with respect to its foreign key footprint; a Data Vault modeling heuristic would therefore suggest treating it as a hub-like record representing the mandate entity itself, since it is not decomposed into separate link or satellite structures in the documented schema. A foreign key relationship exists from IBY_TRXN_SUMMARIES_ALL.DEBIT_AUTHORIZATION_ID to this table.

Key Information Stored

The surrogate primary key is DEBIT_AUTHORIZATION_ID, a mandatory NUMBER(15) column that uniquely identifies each mandate. A documented unique index, SYS_IL0000510751C00028$$, exists in APPS_TS_TX_DATA, but this index is a LOB index associated with the MANDATE_FILE column rather than a conventional business-key candidate.

The most significant columns include:

• AUTHORIZATION_REFERENCE_NUMBER — the mandate reference number used by the creditor and bank.
• INITIAL_DEBIT_AUTHORIZATION_ID and AUTHORIZATION_REVISION_NUMBER — the original mandate and its revision, supporting amendment tracking.
• PAYMENT_TYPE_CODE, DEBIT_AUTH_METHOD, and AMENDMENT_REASON_CODE — the mandate type, collection method, and reason for amendment.
• AUTH_SIGN_DATE and AUTH_CANCEL_DATE — the signing and cancellation dates of the authorization.
• DEBIT_AUTH_BEGIN and DEBIT_AUTH_END — the effective start and end dates of the mandate.
• PRE_NOTIFICATION_REQUIRED_FLAG and DEBIT_AUTH_FLAG — indicators for pre-notification obligations and authorization status.
• CREDITOR_LEGAL_ENTITY_ID, CREDITOR_LE_NAME, and CREDITOR_IDENTIFIER — the payee legal entity and creditor identifier.
• CUST_ADDR_ID and CUST_IDENTIFICATION_CODE — the customer address and identification reference.
• EXTERNAL_BANK_ACCOUNT_USE_ID — links the mandate to the bank account site use.
• MANDATE_FILE — a BLOB holding the scanned or electronic mandate document.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, and LAST_UPDATE_LOGIN — standard audit columns.

Common Use Cases and Queries

Typical reporting scenarios include identifying active mandates for a given bank account, reviewing mandates approaching expiry, and tracing the mandate referenced by a payment transaction. A representative query joining the mandate to its transaction summary is:

• SELECT d.DEBIT_AUTHORIZATION_ID, d.AUTHORIZATION_REFERENCE_NUMBER, d.DEBIT_AUTH_BEGIN, d.DEBIT_AUTH_END FROM IBY.IBY_DEBIT_AUTHORIZATIONS d WHERE d.EXTERNAL_BANK_ACCOUNT_USE_ID = :site_use_id AND d.DEBIT_AUTH_FLAG = 'Y';
• SELECT t.TRXN_SUMMARY_ID, d.AUTHORIZATION_REFERENCE_NUMBER FROM IBY.IBY_TRXN_SUMMARIES_ALL t JOIN IBY.IBY_DEBIT_AUTHORIZATIONS d ON t.DEBIT_AUTHORIZATION_ID = d.DEBIT_AUTHORIZATION_ID;
• Mandate expiry reporting: SELECT * FROM IBY.IBY_DEBIT_AUTHORIZATIONS WHERE DEBIT_AUTH_END BETWEEN SYSDATE AND SYSDATE + 90;

These patterns support collections teams, treasury reconciliation, and audit evidence gathering where the mandate document stored in MANDATE_FILE must be produced.

Related Objects

The most significant dependent object is IBY_TRXN_SUMMARIES_ALL, which references this table through the DEBIT_AUTHORIZATION_ID column and records payment transaction summaries tied to a mandate. The primary key constraint IBY_DEBIT_AUTHORIZATIONS_PK governs the surrogate identifier. The LOB segment SYS_IL0000510751C00028$$ and its associated index support retrieval of the MANDATE_FILE BLOB. The EXTERNAL_BANK_ACCOUNT_USE_ID column associates each mandate with the external bank account use record in the Payments bank account model, while CREDITOR_LEGAL_ENTITY_ID and CUST_ADDR_ID provide linkage to legal entity and customer address data used during mandate validation. Payment process requests, payment instruction creation, and mandate validation logic within Oracle Payments consume this table to confirm authorization before a direct debit is executed.