Results for “iby_completed_pmt_xml_v”

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

Overview

The IBY_COMPLETED_PMT_XML_V view is a reporting and integration object in the Oracle Payments (IBY) module of Oracle E-Business Suite, owned by the APPS schema. It presents completed payment records in an aggregated XML format, exposing a single COMPLETED_CONTENT column of type XMLTYPE alongside several scalar status and identifier columns. The view is designed to provide downstream consumers — external payment systems, clearing houses, and integration middleware — with a consolidated, machine-readable representation of completed payment instructions and their associated payment service requests.

The view derives its name and purpose from the COMPLETED_CONTENT column, which the user searched for under the term "completed_content." This column is the XML payload produced by the XMLCONCAT and XMLAGG functions in the view definition.

Underlying Base Objects

The view is defined over three synonym-backed base objects:

• IBY_PAYMENTS_ALL — the master payment table, aliased PMT, supplying payment identifiers and payment status.
• IBY_PAY_SERVICE_REQUESTS — the payment service request table, aliased REQ, supplying the calling application ID, the payment service request ID, and its status.
• IBY_PAY_INSTRUCTIONS_ALL — the payment instruction table, aliased INSTR, supplying payment instruction identifiers and statuses.

The view joins these objects on PAYMENT_SERVICE_REQUEST_ID (linking requests to payments) and PAYMENT_INSTRUCTION_ID (linking payments to instructions). The XML construction depends on the XMLAGG synonym together with the AGGXMLIMP and XMLTYPE types.

Key Columns

• COMPLETED_CONTENT — the aggregated XML payload. It contains a PAYMENTGROUP element with a nested FNDAPPLICATIONID (the calling application ID) and a collection of COMPLETEDPAYMENT elements, each holding a PAYMENTID via XMLFOREST.
• REQUEST_ID — the payment service request identifier (PAYMENT_SERVICE_REQUEST_ID).
• REQUEST_STATUS — the status of the payment service request.
• PAYMENT_ID — the unique payment identifier.
• PAYMENT_STATUS — the current status of the payment.
• INSTRUCTION_ID — the payment instruction identifier.
• INSTRUCTION_STATUS — the status of the payment instruction.

The grouping is performed on CALLING_APP_ID, the payment service request and its status, the payment ID and status, and the instruction ID and status.

Common Use Cases and Queries

Typical uses include extracting completed-payment XML for transmission to a bank or clearing network, auditing which payments belong to a given payment service request, and reconciling instruction status against payment status. A representative query is:

• SELECT completed_content, request_id, payment_id, payment_status FROM iby_completed_pmt_xml_v WHERE request_status = 'COMPLETED';
• SELECT payment_id, instruction_id, instruction_status FROM iby_completed_pmt_xml_v WHERE instruction_status = 'FORMATTED';
• SELECT v.completed_content FROM iby_completed_pmt_xml_v v WHERE v.request_id = :p_request_id;

Because COMPLETED_CONTENT is an XMLTYPE, consumers may apply XML functions such as EXTRACT or XMLQUERY to navigate the PAYMENTGROUP and COMPLETEDPAYMENT nodes. On both EBS 12.1.1 and 12.2.2 the object remains VALID in the APPS schema.