Search Results ams_relationship_id




Overview
Explore in AI Chat

Overview

AMS_ACCT_SHIP_TO_CONTACT_V is a Marketing (AMS) module view that returns the primary ship-to contact associated with a customer account. As stated in the ETRM metadata, the view is designed to expose the primary ship-to contact for an account and is primarily used for list generation within Oracle Marketing. Because list generation and campaign targeting frequently require identifying a single, authoritative contact per account, this view consolidates the relevant party, account role, and responsibility data into one denormalized row per qualifying ship-to contact.

The view is documented in the ETRM repository for Oracle EBS 12.1.1 and 12.2.2 and is marked as "Not implemented in this database." This means the view definition is catalogued for reference purposes but may not be deployed in every environment. The absence of any documented referenced base objects in the metadata record is consistent with this status; the authoritative object set must instead be read directly from the view's SQL text.

Underlying Base Objects

Although the ETRM metadata lists no referenced base objects, the view text reveals that it is defined over six TCA (Trading Community Architecture) tables joined together:

Joins are driven through CUST_ACCOUNT_ID, PARTY_ID, SUBJECT_ID, and PARTY_RELATIONSHIP_ID, with R.CUST_ACCT_SITE_ID IS NULL ensuring the role applies at the account level rather than a specific site.

Key Columns

Common Use Cases and Queries

The view is typically consumed to drive Marketing list generation, campaign audience selection, and contact selection for accounts where a primary ship-to contact is required.

SELECT ams_contact_id,
       contact_party_id,
       acct_id,
       party_name,
       person_first_name,
       person_last_name
  FROM ams_acct_ship_to_contact_v
 WHERE acct_id = :p_account_id;

For bulk list generation, consumers join the view to account or campaign staging tables using ACCT_ID or AMS_CONTACT_ID, optionally filtering on CURRENT_ROLE_STATE and BEGIN_DATE/END_DATE to restrict results to currently effective roles. Because the view already enforces STATUS = 'A' on parties and accounts, PRIMARY_FLAG = 'Y', and RESPONSIBILITY_TYPE = 'SHIP_TO', callers need not re-apply those predicates in most reporting scenarios.