Results for “iby_xml_fndcpt_acct_1_0_v”

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

Overview

The IBY_XML_FNDCPT_ACCT_1_0_V view is a Payments (IBY) module component in the APPS schema that projects Oracle Payments funds capture transaction data into an XML representation. Specifically, it constructs a PAYEEACCOUNT XML document element that aggregates payment system account information, bank account details, payee identification, order counts, and monetary totals (authorizations, captures, and credits) grouped by batch and payee instrument. Rather than exposing flat relational rows, the view emits an XMLTYPE value built through SQL/XML functions such as XMLELEMENT, XMLFOREST, XMLAGG, and EXTRACT, making it a formatting and integration object rather than a conventional reporting view.

Its role is to serve as a data source for funds capture extract generation. The referenced base objects include packages such as IBY_FNDCPT_EXTRACT_GEN_PVT, IBY_FD_EXTRACT_GEN_PVT, and IBY_EXTRACTGEN_PVT, confirming that this view participates in producing structured output for payment processors and settlement files. The _1_0_ naming convention indicates it implements version 1.0 of a defined XML message schema.

Underlying Base Objects

Per the 12.2.2 ETRM metadata, the view is defined over several synonyms and references multiple PL/SQL packages. The principal query sources are IBY_TRXN_SUMMARIES_ALL (transaction summaries, aliased TXN), IBY_PAYEE (aliased PAYEE), IBY_BEPKEYS (aliased KEYS), the nested view IBY_XML_FNDCPT_BANKACCOUNT_V, IBY_XML_BEP_ACCT_OPTIONS_V, and the related view IBY_XML_FNDCPT_ORDER_1_0_V, which supplies the nested FNDCPT_ORDER elements through XMLAGG.

Additional referenced objects include IBY_BATCHES_ALL, IBY_BEP_ACCT_OPT_VALS, and packages such as IBY_SECURITY_PKG, IBY_UTILITY_PVT, IBY_CREDITCARD_PKG, IBY_AR_UTILS, IBY_EXT_BANKACCT_PUB, and HZ_FORMAT_PUB. XML infrastructure types XMLTYPE and AGGXMLIMP support the aggregation logic. In 12.1.1 the same view is present and VALID, with an equivalent object structure.

Key Columns

The view does not surface discrete columns in the traditional sense; its output is a single XML document with a defined hierarchy. The key logical elements are:

• PAYMENTSYSTEMACCOUNT / ACCOUNTNAME — Derived from TXN.BEPKEY, identifying the payment system account.
• OPTIONSET — Account option values aggregated from the account options view.
• BANKACCOUNT — Nested bank account content sourced from IBY_XML_FNDCPT_BANKACCOUNT_V.
• PAYEE — Payee name, structured address elements (ADDRESSLINE1-3, CITY, STATE, COUNTRY, POSTALCODE), and MCC code (with -1 decoded to NULL).
• ORDERCOUNT — Count of transactions grouped by batch.
• ACCOUNTTOTALS — AUTHORIZATIONSTOTAL, CAPTURESTOTAL, and CREDITSTOTAL elements, each with a VALUE and CURRENCY CODE. Values branch on INSTRTYPE (PINLESSDEBITCARD, BANKACCOUNT) and TRXNTYPEID/REQTYPE.
• MBATCHID / PAYEEINSTRID — Grouping keys exposed for batch and payee instrument identification.

Common Use Cases and Queries

Typical scenarios include extracting funds capture data for payment processor feeds, reconciling batch-level authorization and capture totals, and generating payee account messages. A minimal query follows:

• SELECT * FROM APPS.IBY_XML_FNDCPT_ACCT_1_0_V; — returns XMLTYPE rows.
• SELECT x.* FROM APPS.IBY_XML_FNDCPT_ACCT_1_0_V v, XMLTABLE('/PAYEEACCOUNT' PASSING v.COLUMN_VALUE COLUMNS accountname VARCHAR2(100) PATH 'PAYMENTSYSTEMACCOUNT/ACCOUNTNAME') x; — flattens specific elements for reporting.
• SELECT v.COLUMN_VALUE.getClobVal() FROM APPS.IBY_XML_FNDCPT_ACCT_1_0_V v; — retrieves the full XML as a CLOB for downstream file generation.

Because the view relies on database XML aggregation and is intended for extract generation, filtering should be applied at the underlying summary tables where possible to limit aggregation cost.