Search Results pos_acnt_addr_req_u1




Overview
Explore in AI Chat

Overview

POS.POS_ACNT_ADDR_REQ is a transactional table in the Oracle E-Business Suite POS (Payables Open Interface / Supplier Banking) schema that stores supplier bank account and address assignment requests. It functions as the operational record of requests to associate a supplier bank account or address with a specific supplier party site, capturing the lifecycle of each assignment as it moves through the request status workflow. The table resides in the APPS_TS_TX_DATA tablespace with a PCT Free of 10, and its indexes are held in APPS_TS_TX_IDX, reflecting a standard OLTP configuration for transactional data in Oracle EBS 12.1.1 and 12.2.2.

From a modeling perspective, the heuristic Data Vault classification for this object is satellite-leaning. The table's structure — a surrogate primary key (ASSIGNMENT_REQUEST_ID) combined with descriptive attributes (REQUEST_STATUS, REQUEST_TYPE, OBJECT_VERSION_NUMBER) and foreign keys pointing to POS_SUPPLIER_MAPPINGS, POS_ADDRESS_REQUESTS, and HZ_PARTY_SITES — suggests it behaves as a satellite record attached to core supplier, party site, and mapping hubs, capturing the changing state of each assignment request. This is a modeling suggestion, not a documented fact.

Key Information Stored

The table contains 12 documented columns. The most significant are:

  • ASSIGNMENT_REQUEST_ID (NUMBER, 15) — Primary key for the table and the sole column of the unique index POS_ACNT_ADDR_REQ_U1. This is the surrogate identifier for each assignment request record.
  • MAPPING_ID (NUMBER, 15) — Foreign key to POS_SUPPLIER_MAPPINGS, linking the request to the underlying supplier mapping configuration.
  • ADDRESS_REQUEST_ID (NUMBER, 15) — Foreign key to POS_ADDRESS_REQUESTS, tying the assignment to a previously submitted address request.
  • PARTY_SITE_ID (NUMBER, 15) — Foreign key to HZ_PARTY_SITES, identifying the trading party site to which the account or address is being assigned.
  • REQUEST_STATUS (VARCHAR2, 30) — The current state of the assignment request (e.g., pending, processed, rejected).
  • REQUEST_TYPE (VARCHAR2, 30) — Categorizes the nature of the request, distinguishing between account assignment and address assignment operations.
  • OBJECT_VERSION_NUMBER (NUMBER, 15) — Optimistic locking column used by the Oracle Application Framework (OAF) to detect concurrent updates.
  • CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN — Standard Who columns capturing audit information for creation and last modification, including the login session.

The only documented unique index is POS_ACNT_ADDR_REQ_U1 on ASSIGNMENT_REQUEST_ID, making it the primary business-key candidate alongside the primary key constraint POS_ACNT_ADDR_REQ_PK. Three non-unique indexes support query access on MAPPING_ID, PARTY_SITE_ID, and ADDRESS_REQUEST_ID respectively.

Common Use Cases and Queries

This table is queried primarily to determine the status of supplier bank account and address assignment requests, to trace a request back to its mapping configuration, and to reconcile assignments against party sites. Typical reporting scenarios include operational dashboards tracking pending or failed assignment requests, and troubleshooting queries linking a request to its source mapping or address request.

A representative query pattern joining the table to its related objects:

  • SELECT r.ASSIGNMENT_REQUEST_ID, r.REQUEST_STATUS, r.REQUEST_TYPE, m.<mapping_column>, s.PARTY_SITE_ID FROM POS.POS_ACNT_ADDR_REQ r JOIN POS.POS_SUPPLIER_MAPPINGS m ON r.MAPPING_ID = m.MAPPING_ID JOIN HZ.HZ_PARTY_SITES s ON r.PARTY_SITE_ID = s.PARTY_SITE_ID WHERE r.REQUEST_STATUS = 'PENDING';
  • Filtering by PARTY_SITE_ID to enumerate all assignment requests affecting a given site.
  • Filtering by ADDRESS_REQUEST_ID to trace the downstream account/address assignment resulting from a specific address request.

The POS_ACNT_ADDR_REQ_U1 index ensures fast lookup by ASSIGNMENT_REQUEST_ID, while the N1, N2, and N3 indexes accelerate joins and filters on MAPPING_ID, PARTY_SITE_ID, and ADDRESS_REQUEST_ID respectively.

Related Objects

The following objects are directly related through documented foreign key relationships:

  • POS.POS_SUPPLIER_MAPPINGS — Referenced by MAPPING_ID; holds the supplier mapping definition underlying the request.
  • POS.POS_ADDRESS_REQUESTS — Referenced by ADDRESS_REQUEST_ID; the source address request record.
  • HZ.HZ_PARTY_SITES — Referenced by PARTY_SITE_ID; identifies the party site receiving the assignment.
  • POS.POS_ACNT_ADDR_SUMM_REQ — References this table via ASSIGNMENT_REQUEST_ID; the summary-level record aggregating assignment requests.
  • POS.POS_ACNT_ADDR_REQ# — The supporting database object (sequence/view) associated with this table.

No database objects are referenced by POS_ACNT_ADDR_REQ that are not already captured in the foreign key list above. The table participates in the broader supplier and party-site assignment flow, with POS_ACNT_ADDR_SUMM_REQ serving as the principal downstream consumer of its ASSIGNMENT_REQUEST_ID values.