Search Results iby_xml_fd_ins_1_0_v




Overview
Explore in AI Chat

Overview

IBY_XML_FD_INS_1_0_V is a database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the IBY (Payments) product module. It is a component of the Oracle Payments XML file delivery infrastructure and generates an XML document conforming to the ISO 20022-style "OutboundPaymentInstruction" structure. The view exists in both Oracle EBS 12.1.1 and 12.2.2 with a VALID status, and its definition is oriented entirely around XML publishing rather than conventional relational reporting.

The view produces a hierarchical XMLTYPE result by chaining a series of XMLELEMENT calls against payment instruction data. Each row emitted by the view represents a single payment instruction and its nested attributes, including instruction identification, status, processing type, bank instructions, payment sequencing, account settings, and regulatory reporting options. It serves as the outbound XML generation layer used when Oracle Payments delivers payment instructions to a bank or clearing house in a structured, standards-aligned format.

Underlying Base Objects

The view is not defined over conventional base tables alone. The ETRM metadata records the following referenced objects:

  • IBY_EXT_FD_INS_1_0_V (VIEW) — the principal source view supplying the formatted instruction-level columns consumed by the XMLELEMENT projections.
  • IBY_FD_EXTRACT_GEN_PVT (PACKAGE) — extraction generation logic, including the function GET_INS_ACCOUNTSETTINGSAGG invoked to aggregate payment system account settings into XML for the PAYMENTSYSTEMACCOUNT element.
  • IBY_EXTRACTGEN_PVT (PACKAGE) — supporting extraction routines used during XML payload assembly.
  • IBY_FD_POST_PICP_PROGS_PVT (PACKAGE) — post-processing logic associated with payment instruction completion programs.
  • IBY_EXT_WRAP_PUB (PACKAGE) — XML wrapping and document envelope handling.
  • FND_CURRENCIES_VL (VIEW) and FND_GLOBAL (PACKAGE) — standard EBS foundation objects used for currency resolution and session/context values.
  • XMLTYPE (TYPE) — the Oracle XML DB type that defines the return datatype of the view.

The layering is intentional: the raw payment instruction data is normalized and prepared in the extract views and private packages, and IBY_XML_FD_INS_1_0_V performs the final serialization into the outbound XML message shape.

Key Columns

Although the view returns a single XMLTYPE column, the meaningful fields are the XML elements it constructs. The most significant include:

  • INSTRUCTIONREFERENCENUMBER — derived from PAYMENT_INSTRUCTION_ID; uniquely identifies the payment instruction within Oracle Payments.
  • INSTRUCTIONCREATIONDATE — the instruction creation timestamp formatted as YYYY-MM-DD"T"HH24:MI:SS, a common XML/ISO date representation.
  • INSTRUCTIONSTATUS — a composite element carrying both CODE (the internal status code) and MEANING (the user-facing status description).
  • PROCESSINGTYPE — indicates how the instruction is processed by the payment system.
  • USERASSIGNEDREFCODE and BANKASSIGNEDREFCODE — reference codes assigned by the pay administrator and the bank respectively.
  • PAYMENTSEQUENCE — repeated up to three times, each pairing a SEQUENCENAME with a LASTVALUE drawn from the periodic sequence columns.
  • BANKINSTRUCTION — repeated elements containing BANKINSTRUCTIONCODE, MEANING, and FORMATVALUE.
  • BANKINSTRUCTIONDETAILS — free-form supplementary bank instruction text.
  • PAYMENTTEXTMESSAGE — messages one and two, providing remittance or narrative text.
  • REGULATORYREPORTINGOPTION — populated from DECLARATION_OPTION.
  • PAYMENTSYSTEMACCOUNT — includes ACCOUNTNAME and the aggregated account settings returned by GET_INS_ACCOUNTSETTINGSAGG.

Common Use Cases and Queries

Typical uses center on inspecting, debugging, or extracting the outbound XML generated for a specific payment instruction. The view is most often queried directly to see the serialized payload:

  • Single instruction inspection — SELECT * FROM APPS.IBY_XML_FD_INS_1_0_V WHERE EXTRACTVALUE(...) ... to examine the XML for a given instruction reference.
  • Bulk validation — counting rows or filtering by status to confirm which instructions produce valid XML before transmission.
  • Integration troubleshooting — confirming that account settings, bank instructions, and sequences are correctly embedded when a payment file fails downstream bank validation.
  • Downstream transformation — using the XMLTYPE output as input to XSLT or a middleware mapping layer.

Because the view relies on package calls and XMLTYPE construction, it is not intended for high-volume relational reporting; performance-sensitive extraction should target the underlying extract views instead.