Results for “pseudo_key”
12 results
AI-generated from documented ETRM metadata — verify critical details on the linked pages.
Overview
SY_ADDR_MST is the OPM Address Master table within the Oracle Process Manufacturing (OPM) schema GMA. It serves as the central repository for postal and physical address records used throughout Process Manufacturing Systems. In Oracle EBS 12.1.1 and 12.2.2, this table is a foundational reference object: rather than duplicating address lines across dozens of transactional and master tables, OPM stores each address once in SY_ADDR_MST and references it by the surrogate key ADDR_ID.
The object is owned by the GMA schema and is classified as VALID. Its documented physical schema in ETRM 12.2.2 contains 24 columns. Because the table is referenced by an extensive set of foreign keys rather than itself acting as a dependent child, a heuristic Data Vault classification of hub-leaning is reasonable. In Data Vault terms, SY_ADDR_MST behaves much like a hub: ADDR_ID is a stable, system-assigned identifier, and the many inbound foreign keys represent links to that hub. Modeling this as a hub with attached descriptive satellites would preserve its role as the authoritative address identity anchor.
Key Information Stored
The table's primary key is defined by the constraint SY_ADDR_MST_PK on the column ADDR_ID, a surrogate identifier assigned by the application. Business users do not typically supply this value; it is generated and then propagated as a foreign key to dependent tables. Address content is spread across the columns ADDR1, ADDR2, ADDR3, and ADDR4, with ORA_ADDR4 also documented as a physical column, suggesting a flexible or overflow address-line field. Geographic attributes include STATE_CODE, COUNTRY_CODE, POSTAL_CODE, PROVINCE, and COUNTY. The PSEUDO_KEY column provides an alternate lookup handle often used by OPM forms and imports to locate an address without relying on the numeric surrogate. Descriptive text is held in COMMENTS.
Audit and concurrency columns follow EBS conventions: CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, and LAST_UPDATE_LOGIN. The column TRANS_CNT supports optimistic locking by tracking the transaction count for a row. DELETE_MARK implements soft deletion, allowing rows to be logically retired without physical removal. Standard concurrent program context is captured by PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE, and REQUEST_ID. No unique business-key index on the geographic attributes is documented, so ADDR_ID remains the only guaranteed unique identifier and should be treated as the primary key throughout.
Common Use Cases and Queries
Typical use cases include resolving ship-to, bill-to, receiving, mailing, and vendor addresses for OPM customers, vendors, warehouses, sales representatives, and organizations. A frequent pattern joins a transactional table to SY_ADDR_MST through the relevant address column. For example, retrieving order ship-to details:
SELECT h.ORDER_NO, a.ADDR1, a.ADDR2, a.STATE_CODE, a.POSTAL_CODE FROM OP_ORDR_HDR h, SY_ADDR_MST a WHERE h.SHIPADDR_ID = a.ADDR_ID;SELECT v.VENDOR_NO, a.ADDR1, a.COUNTRY_CODE FROM PO_VEND_MST v, SY_ADDR_MST a WHERE v.ADDR_ID = a.ADDR_ID;- Reporting by geography: grouping orders or receipts by COUNTRY_CODE, STATE_CODE, or POSTAL_CODE for distribution analysis.
- Data cleanup: filtering on DELETE_MARK to exclude retired addresses, and reviewing TRANS_CNT or LAST_UPDATE_DATE for stale records.
Because many foreign keys target this table, report authors should always qualify join columns and confirm whether a dependent table uses ADDR_ID, MAILADDR_ID, SHIPADDR_ID, RECVADDR_ID, BOLADDR_ID, RTRNADDR_ID, or ORGNADDR_ID, as each represents a distinct business role for the same address master row.
Related Objects
The following tables reference SY_ADDR_MST through documented foreign keys and are the most significant consumers of its address records:
- OP_CUST_MST — customer master, via ADDR_ID and MAILADDR_ID.
- OP_ORDR_HDR and OP_ORDR_DTL — sales order header and detail, via SHIPADDR_ID.
- PO_VEND_MST — vendor master, via ADDR_ID and MAILADDR_ID.
- PO_ORDR_HDR and PO_ORDR_DTL — purchase order header and detail, via RECVADDR_ID.
- OP_SHIP_MST — shipment master, via ADDR_ID.
- IC_WHSE_MST — warehouse master, via ADDR_ID.
- SY_ORGN_MST — organization master, via ADDR_ID.
- OP_LKBX_MST — lockbox master, via ADDR_ID, the object referenced in the originating search.
- TX_TAXA_MST — tax authority master, via ADDR_ID.
- OP_PRSL_HDR and OP_PRSL_DTL — proposal header and detail, via SHIPADDR_ID.
Together these dependencies confirm SY_ADDR_MST as a shared reference hub whose integrity directly affects customer, vendor, order, shipment, warehouse, and tax processing across Oracle Process Manufacturing.
-
OPM Address master. Table which stores addresses for all Oracle Process
-
OPM Address master. Table which stores addresses for all Oracle Process
-
VIEW: GMA.SY_ADDR_MST# 12.2.2
-
VIEW: GMA.SY_ADDR_MST# 12.2.2
-
TABLE: GMA.SY_ADDR_MST 12.2.2
-
TABLE: GMA.SY_ADDR_MST 12.1.1
-
eTRM - GMA Tables and Views 12.2.2
Table for associating Units of Measure and UOM Types. Not yet used.
-
eTRM - GMA Tables and Views 12.1.1
Table for associating Units of Measure and UOM Types. Not yet used.