Results for “iby_trxn_documents_u2”

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

Overview

IBY_TRXN_DOCUMENTS is a table in the IBY (Payments) schema within Oracle E-Business Suite, available in both 12.1.1 and 12.2.2. It serves as the XML document repository for payment transactions, storing the structured XML payloads that accompany a given transaction, such as credit application documents, terms and conditions, and payment messages. Each row associates a transaction (identified by TRXNMID) with a specific document type (DOCTYPE) and stores the corresponding XML content in a LOB column.

From a Data Vault modeling perspective, the mined foreign-key structure suggests a hub-leaning classification. The table's natural business key is the composite of TRXNMID and DOCTYPE, which identifies the transaction-to-document-type relationship, while the surrogate key TRXN_DOCUMENT_ID provides row-level identity. This pattern is consistent with a hub that also carries descriptive satellite attributes.

Key Information Stored

The most significant columns in this table include:

• TRXN_DOCUMENT_ID — Surrogate primary key (IBY_TRXN_DOCUMENTS_PK / unique index IBY_TRXN_DOCUMENTS_U1) providing unique row identity.
• TRXNMID — Transaction identifier; part of the business-key unique index and the FK to IBY_TRXN_SUMMARIES_ALL.
• DOCTYPE — Document type discriminator; combined with TRXNMID forms the unique business key IBY_TRXN_DOCUMENTS_U2.
• DOCUMENT — The XML document payload (LOB) associated with the transaction.
• PAYMENT_INSTRUCTION_ID — FK linking the document to a payment instruction in IBY_PAY_INSTRUCTIONS_ALL.
• FROZEN_FLAG — Indicates whether the document is locked from further modification.
• TO_BE_PURGED_FLAG and DOCUMENT_PURGED_FLAG / DOCUMENT_PURGED_DATE — Purge-management controls for archiving XML content.
• OBJECT_VERSION_NUMBER — Optimistic locking version used by the ORM layer.
• REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — Concurrent-request provenance for auditing.
• SECURITY_GROUP_ID — Multi-tenant / security group partitioning, FK to FND_SECURITY_GROUPS.
• Standard audit columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN.

Common Use Cases and Queries

This table is queried whenever payment XML content must be retrieved for reconciliation, troubleshooting, or regulatory archiving. Typical scenarios include reconstructing credit application XML, extracting terms and conditions for a payment, and auditing message XML associated with a payment transaction.

• Retrieve XML for a specific transaction and document type:
  SELECT document FROM iby_trxn_documents WHERE trxnmid = :trxnmid AND doctype = :doctype;
• Locate documents awaiting purge:
  SELECT trxn_document_id FROM iby_trxn_documents WHERE to_be_purged_flag = 'Y' AND document_purged_flag = 'N';
• Join to transaction summaries for reporting:
  SELECT d.doctype, d.creation_date FROM iby_trxn_documents d, iby_trxn_summaries_all s WHERE d.trxnmid = s.trxnmid;
• Identify documents by payment instruction:
  SELECT * FROM iby_trxn_documents WHERE payment_instruction_id = :pi_id;

Related Objects

The principal related objects are:

• IBY_TRXN_SUMMARIES_ALL — Parent transaction summary; joined via TRXNMID.
• IBY_PAY_INSTRUCTIONS_ALL — Payment instruction source; joined via PAYMENT_INSTRUCTION_ID.
• FND_SECURITY_GROUPS — Security group definitions; joined via SECURITY_GROUP_ID.
• IBY_F_T_CRDT_APPS_ALL_B — References this table via CREDIT_APP_XML_ID and TERMS_N_CONDITIONS_XML_ID.
• IBY_G_T_MESSAGES — References this table via MESSAGE_XML_ID.