Search Results extract_mode

Overview

APPS.IBY_XML_BATCH_FCI_1_0_V is an XML-generating view in the Oracle E-Business Suite Payments (IBY) module. It produces a FundsCaptureInstruction document — the FCI message used by Oracle Payments to transmit funds capture (receivable) instructions to a payment system or processor. The view consolidates payment batch header data from IBY_BATCHES_ALL and transaction summary totals from IBY_TRXN_SUMMARIES_ALL into a structured XMLType payload conforming to the Funds Capture Instruction 1.0 schema.

The view is a runtime artifact rather than a persistent table: it renders XML on demand by invoking PL/SQL APIs and nested XML views. It is referenced during funds capture extract generation, where a batch is serialized into an instruction message consumed by downstream settlement, gateway, or file-transfer processes. Because the output is XML, the view supports both EBS-native integration flows and external clearing-house submissions.

Underlying Base Objects

The view is defined primarily over the synonym IBY_BATCHES_ALL (aliased batch) and correlated subqueries against IBY_TRXN_SUMMARIES_ALL (aliased txpi) to derive the distinct payee account count. It nests three additional XML views — IBY_XML_FNDCPT_ACCT_1_0_V, IBY_XML_FNDCPT_PAYEE_1_0_V, and IBY_XML_FC_PRBA_1_0_V — to embed payee account, legal entity, and bank account fragments.

Numerous PL/SQL packages are referenced: IBY_UTILITY_PVT (which supplies get_view_param), IBY_FNDCPT_EXTRACT_GEN_PVT (which supplies Get_Ins_PayeeAcctAgg), plus IBY_EXTRACTGEN_PVT, IBY_EXT_BANKACCT_PUB, IBY_FD_EXTRACT_GEN_PVT, IBY_SECURITY_PKG, IBY_CREDITCARD_PKG, IBY_TRANSACTIONCC_PKG, IBY_AR_UTILS, FND_GLOBAL, and HZ_FORMAT_PUB. XML aggregation relies on the XMLAGG synonym and AGGXMLIMP/XMLTYPE types. These dependencies mean the view inherits security context from IBY_SECURITY_PKG and adapts its output shaping based on extract mode.

Key Columns

The view exposes a single XML document column rather than relational columns. Its logical structure includes:

• InstructionInternalID — derived from batch.mbatchid, the internal batch identifier.
• InstructionName — the user-visible batch.batchid.
• InstructionCreationDate / InstructionSentDate — formatted as ISO 8601 (YYYY-MM-DD"T"HH24:MI:SS), with the sent date using SYSDATE.
• InstructionStatus — carries batch.batchstatus with a null meaning placeholder.
• InstructionSequence — sequence name SENT_COUNTER_DAILY with NVL(batch.sentcounterdaily,1).
• PayeeAccountCount — a distinct count of bepkey values from IBY_TRXN_SUMMARIES_ALL.
• SettlementTotal — batch.batchtotal with batch.currencynamecode.
• InstructionGrouping — conditionally embeds settlement date, settlement currency, payee legal entity, payee organization, and payee bank account fragments, each guarded by null checks.

The final decode against get_view_param('EXTRACT_MODE') determines whether payee accounts are aggregated via Get_Ins_PayeeAcctAgg (the G_EXTRACT_MODE_SRA path) or via an XMLAgg over IBY_XML_FNDCPT_ACCT_1_0_V.

Common Use Cases and Queries

The view is typically queried during extract generation or troubleshooting of funds capture output. A basic retrieval for a specific batch:

• SELECT * FROM APPS.IBY_XML_BATCH_FCI_1_0_V WHERE ...; — using conditions on mbatchid to isolate a batch.
• Extracting the XML as a CLOB: SELECT XMLSERIALIZE(CONTENT x AS CLOB) FROM ....
• Inspecting a node value: SELECT EXTRACTVALUE(x, '/FundsCaptureInstruction/InstructionInfo/InstructionName') FROM ....

Because the EXTRACT_MODE value drives divergent payee-account aggregation, testers investigating the behavior of get_view_param('EXTRACT_MODE') can toggle the session parameter and observe whether G_EXTRACT_MODE_SRA output appears. Operationally, this view underpins the FCI file delivered to processors, so validating the emitted SettlementTotal, currency, and PayeeAccountCount against the batch is a routine reconciliation step.