Results for “query_trans_prtcl_code”

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

Overview

IBY_FNDCPT_SYS_DC_PF_B is a Payments module table residing in the IBY schema. It stores system-level direct debit profile definitions used by Oracle Payments (IBY) to control how payment processing for a given payment system is grouped, limited, formatted, transmitted, and acknowledged. In Oracle EBS 12.1.1 and 12.2.2 the table functions as the configuration backbone for direct debit instruction creation and settlement, supplying the runtime parameters that drive payment process profile behavior at the system level.

The heuristic Data Vault classification mined from the foreign-key structure suggests a link modeling role. The table sits at the intersection of a payment system, message formats, transmission protocols, and acknowledgment readers, and its rows connect these independent reference entities rather than describing a single business entity's attributes over time. This classification should be treated as a modeling suggestion for warehouse or analytics designs, not as a functional declaration.

Three foreign-key families dominate the design: references to IBY_BEPINFO for the payment system, references to IBY_FORMATS_B for message formats, and references to IBY_TRANSMIT_PROTOCOLS_B for transport protocols. Acknowledgment reader code columns appear as self-referential or unresolved foreign keys in the documented metadata.

Key Information Stored

The documented physical schema contains 53 columns in release 12.2.2. The primary key is the single-column constraint IBY_FNDCPT_SYS_DC_PF_B_PK on SYS_DC_PROFILE_CODE. A separate unique index, IBY_FNDCPT_SYS_DC_PF_B_SPC_U1, spans SYS_DC_PROFILE_CODE and ZD_EDITION_NAME and is the documented business-key candidate, indicating that profile code plus edition name uniquely identifies a configuration version.

• SYS_DC_PROFILE_CODE — surrogate primary key and the principal identifier for the direct debit system profile.
• PAYMENT_SYSTEM_ID — foreign key to IBY_BEPINFO, tying the profile to a specific payment system.
• ONLINE_DEB_FORMAT_CODE, SETTLEMENT_FORMAT_CODE, QUERY_FORMAT_CODE — format definitions for online debit, settlement, and query messages; each joins to IBY_FORMATS_B.
• ONLINE_DEB_TRANS_PRTCL_CODE, SETTLEMENT_TRANS_PRTCL_CODE, QUERY_TRANS_PRTCL_CODE — transport protocol codes joining to IBY_TRANSMIT_PROTOCOLS_B.
• ONLINE_DEB_ACK_RDR_CODE, SETTLEMENT_ACK_RDR_CODE, QUERY_ACK_RDR_CODE — acknowledgment reader codes used to interpret responses for each message type.
• SETTLEMENT_REQ_FLAG — indicates whether a settlement step is required for the profile.
• GROUP_BY_ORG, GROUP_BY_LEGAL_ENTITY, GROUP_BY_INT_BANK_ACCOUNT, GROUP_BY_SETTLEMENT_CURR, GROUP_BY_SETTLEMENT_DATE — grouping controls that determine how instructions are aggregated before formatting.
• LIMIT_BY_AMT_CURR, LIMIT_BY_EXCH_RATE_TYPE, LIMIT_BY_TOTAL_AMT, LIMIT_BY_SETTLEMENT_NUM — thresholds and currency or exchange-rate qualifiers governing aggregation limits.
• PAYER_NOTIFICATION_FORMAT, PAYER_NOTIFICATION_DEL_METHOD, AUTOMATIC_PN_SUBMIT_FLAG, PAYER_NOTIFICATION_CONDITION, PN_COND_NUM_OF_RECEIPTS — payer notification formatting, delivery method, automatic submission, and triggering conditions.
• INACTIVE_DATE — date after which the profile is no longer active.
• OBJECT_VERSION_NUMBER — optimistic locking column maintained by the Oracle Applications framework.
• ZD_EDITION_NAME — editioning discriminator forming part of the unique business key.

Common Use Cases and Queries

Typical usage centers on resolving the formats, protocols, and acknowledgment readers that apply to a payment system, and on validating aggregation and notification settings before enabling a profile in production.

• Profile lookup by payment system, joining to IBY_BEPINFO to confirm the owning system:

SELECT p.SYS_DC_PROFILE_CODE,
       b.PAYMENT_SYSTEM_NAME,
       p.SETTLEMENT_REQ_FLAG,
       p.GROUP_BY_ORG,
       p.GROUP_BY_SETTLEMENT_DATE
FROM   IBY.IBY_FNDCPT_SYS_DC_PF_B p,
       IBY.IBY_BEPINFO b
WHERE  p.PAYMENT_SYSTEM_ID = b.PAYMENT_SYSTEM_ID
AND    p.INACTIVE_DATE IS NULL;

• Resolving message configuration across formats and protocols for auditing:

SELECT p.SYS_DC_PROFILE_CODE,
       f.FORMAT_CODE,
       t.TRANSMIT_PROTOCOL_CODE
FROM   IBY.IBY_FNDCPT_SYS_DC_PF_B p,
       IBY.IBY_FORMATS_B f,
       IBY.IBY_TRANSMIT_PROTOCOLS_B t
WHERE  p.SETTLEMENT_FORMAT_CODE      = f.FORMAT_CODE
AND    p.SETTLEMENT_TRANS_PRTCL_CODE = t.TRANSMIT_PROTOCOL_CODE;

• Reporting active profiles with payer notification automation enabled, filtering on AUTOMATIC_PN_SUBMIT_FLAG and INACTIVE_DATE.
• Extracting aggregation policy for reconciliation analysis using the GROUP_BY_* and LIMIT_BY_* columns.
• Verifying uniqueness and edition management through the SYS_DC_PROFILE_CODE plus ZD_EDITION_NAME unique index.

Related Objects

• IBY_BEPINFO — referenced through PAYMENT_SYSTEM_ID; the primary parent for the profile.
• IBY_FORMATS_B — referenced by PAYER_NOTIFICATION_FORMAT, ONLINE_DEB_ACK_RDR_CODE, SETTLEMENT_FORMAT_CODE, and QUERY_FORMAT_CODE.
• IBY_TRANSMIT_PROTOCOLS_B — referenced by ONLINE_DEB_TRANS_PRTCL_CODE, SETTLEMENT_TRANS_PRTCL_CODE, and QUERY_TRANS_PRTCL_CODE.
• IBY_FNDCPT_SYS_DC_PF_B (self) — referenced by QUERY_ACK_RDR_CODE, ONLINE_DEB_ACK_RDR_CODE, and SETTLEMENT_ACK_RDR_CODE, forming acknowledgment reader lookups.
• Payment process profile configuration — downstream runtime objects in the Payments module that consume the format, protocol, grouping, and notification settings defined here.