Search Results iby_debit_authorizations_pk

Overview

IBY_DEBIT_AUTHORIZATIONS is a transaction and configuration table in the IBY (Payments) product schema of Oracle E-Business Suite, present in both release 12.1.1 and 12.2.2. It stores debit authorization mandate information associated with bank accounts, capturing the legal agreements that permit an organization to collect funds directly from a customer's or counterparty's account. In the payments lifecycle, records in this table act as the governing instrument that validates whether a direct debit instruction is authorized before payment processing proceeds. The table is owned by the IBY schema and carries a documented column count of 28 in the 12.2.2 physical schema.

The metadata classifies this object heuristically as standalone within a Data Vault modeling exercise. This suggests the table functions as a standalone hub or reference entity rather than a link table, since it is referenced by other tables through foreign keys but does not itself carry multiple inbound hub relationships. Modelers should treat DEBIT_AUTHORIZATION_ID as the natural business key candidate for a hub construct, with the descriptive attributes suitable for a satellite.

Key Information Stored

The surrogate primary key is DEBIT_AUTHORIZATION_ID, enforced by the IBY_DEBIT_AUTHORIZATIONS_PK constraint. This column is the join anchor to dependent transaction tables. Business-key and descriptive columns of primary importance include:

• AUTHORIZATION_REFERENCE_NUMBER — the external reference that identifies the mandate at the bank or creditor level.
• INITIAL_DEBIT_AUTHORIZATION_ID — the originating authorization when a record represents a revision or amendment lineage.
• AUTHORIZATION_REVISION_NUMBER — tracks successive revisions of a mandate.
• EXTERNAL_BANK_ACCOUNT_USE_ID — links the mandate to the external bank account usage record.
• PAYMENT_TYPE_CODE and DEBIT_AUTH_METHOD — define the payment instrument and the method by which authorization is captured.
• AUTH_SIGN_DATE / AUTH_CANCEL_DATE and DEBIT_AUTH_BEGIN / DEBIT_AUTH_END — the signing, cancellation, and effective date ranges of the mandate.
• PRE_NOTIFICATION_REQUIRED_FLAG — indicates whether advance notice is required before debiting.
• CREDITOR_LEGAL_ENTITY_ID, CREDITOR_LE_NAME, CREDITOR_IDENTIFIER — the creditor party details.
• CUST_ADDR_ID, CUST_IDENTIFICATION_CODE — customer address and identification attributes.
• DEBIT_AUTH_FLAG, CURR_REC_INDI, DEBIT_AUTH_REFERENCE, MANDATE_FILE — current-record indicator, authorization status flag, and stored mandate document reference.
• Standard audit columns: CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, OBJECT_VERSION_NUMBER.

The only unique index documented is SYS_IL0000510751C00028$$, which is a LOB index rather than a conventional business-key uniqueness constraint.

Common Use Cases and Queries

Typical scenarios include verifying that a customer mandate is active before generating a direct debit, reporting mandates nearing expiration, and auditing authorization revisions. A representative query pattern joins the table to transaction summaries through the foreign key:

• Active mandate lookup: SELECT debit_authorization_id, authorization_reference_number FROM iby.iby_debit_authorizations WHERE debit_auth_flag = 'Y' AND SYSDATE BETWEEN debit_auth_begin AND debit_auth_end;
• Revision lineage: filter on INITIAL_DEBIT_AUTHORIZATION_ID to trace amendments.
• Expiry reporting: filter on DEBIT_AUTH_END against a rolling window for compliance follow-up.
• Transaction linkage: join IBY_TRXN_SUMMARIES_ALL.DEBIT_AUTHORIZATION_ID to the primary key to associate payments with their governing mandates.

Related Objects

The documented foreign key relationship shows IBY_TRXN_SUMMARIES_ALL.DEBIT_AUTHORIZATION_ID referencing this table, making it the principal dependent object for payment transaction reconciliation. Additional relevant objects in the IBY payments model include IBY_BANKACCT_USE_ALL (bank account usage), IBY_EXTERNAL_PAYEES_ALL and IBY_PAYEES_ALL (party records referenced through creditor and customer identifiers), IBY_PAYMENTS_ALL (payment instruments), and the external bank account tables referenced via EXTERNAL_BANK_ACCOUNT_USE_ID. Mandate document storage associated with MANDATE_FILE links to the FND document and attachment infrastructure. These relationships support end-to-end tracing from mandate capture through payment execution and summary reporting.