Results for “ams_org_details_v”

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

Overview

AMS_ORG_DETAILS_V is a Marketing (AMS) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. As documented in the ETRM metadata, it lists the details of an Organization, consolidating party-level, party-site-level, and organization-profile-level attributes into a single denormalized read interface. The view is defined over the Trading Community Architecture (TCA) model, drawing on HZ_PARTIES, HZ_PARTY_SITES, HZ_LOCATIONS, HZ_ORGANIZATION_PROFILES, and HZ_CONTACT_POINTS through public synonyms. Because organizations (customers, prospects, and marketing entities) are central to campaign targeting, list generation, and lead qualification, this view serves as a convenient reporting and integration access point that shields consumers from the underlying multi-table TCA joins. It is a read-only construct; no DML is supported against it, and it is marked VALID in the data dictionary.

Underlying Base Objects

The view is anchored on HZ_PARTIES, which supplies the party identity and standard attributes (PARTY_ID, PARTY_NUMBER, PARTY_NAME, PARTY_TYPE, STATUS, creation and audit columns). HZ_PARTY_SITES contributes site-level data such as PARTY_SITE_ID, PARTY_SITE_NAME, REGION, MAILSTOP, LANGUAGE, and IDENTIFYING_ADDRESS_FLAG. HZ_LOCATIONS is referenced through the party-site relationship to support address and geographical attributes. HZ_ORGANIZATION_PROFILES delivers the firmographic and profile attributes prefixed HOP., including ORGANIZATION_NAME, CEO_NAME, SIC_CODE, EMPLOYEES_TOTAL, and the credit/failure scoring columns. HZ_CONTACT_POINTS is listed as a referenced base object and supplies contact mechanisms, most relevantly the PRIMARY_CONTACT_ID that ties in primary contact information.

Key Columns

• PARTY_ID, PARTY_NUMBER, PARTY_NAME, PARTY_TYPE, STATUS — core party identification and classification.
• PARTY_SITE_ID, PARTY_SITE_NAME, REGION, MAILSTOP, LANGUAGE, IDENTIFYING_ADDRESS_FLAG — site-specific context.
• ADDRESS1–ADDRESS4, CITY, STATE, POSTAL_CODE, PROVINCE, COUNTY, COUNTRY — postal address detail.
• SIC_CODE, SIC_CODE_TYPE, EMPLOYEES_TOTAL, LEGAL_STATUS, CEO_NAME, CEO_TITLE, LINE_OF_BUSINESS — firmographic profile data.
• PRIMARY_CONTACT_ID — the identifier of the organization's primary contact, the column of interest in the originating search.
• CREDIT_SCORE, DB_RATING, FAILURE_SCORE, PAYDEX_SCORE, GLOBAL_FAILURE_SCORE — credit and risk scoring.
• CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, PROGRAM_UPDATE_DATE, WH_UPDATE_DATE, REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID — standard WHO/audit and concurrent program columns.

Common Use Cases and Queries

Typical uses include building organization lists for campaign targeting, enriching lead records with firmographic and credit data, and resolving the primary contact for an organization.

• Retrieve organization details for a given party:

  SELECT party_id, party_name, party_type, status, organization_name, sic_code FROM ams_org_details_v WHERE party_id = :p_party_id;

• Locate organizations by party number and surface the primary contact:

  SELECT party_number, party_name, primary_contact_id FROM ams_org_details_v WHERE party_number = :p_number;

• Filter organizations by industry and size for targeting:

  SELECT party_id, organization_name, employees_total FROM ams_org_details_v WHERE sic_code = :p_sic AND employees_total > :p_min;

• Identify organizations for which no primary contact is recorded:

  SELECT party_id, party_name FROM ams_org_details_v WHERE primary_contact_id IS NULL;

Because PRIMARY_CONTACT_ID originates in HZ_ORGANIZATION_PROFILES, it identifies the contact party associated with the organization profile; consumers should join to HZ_PARTIES or the contact tables when full contact detail is required. Queries benefit from predicates on PARTY_ID, PARTY_NUMBER, or PARTY_SITE_ID, which map to indexed TCA keys.