Search Results ams_organization

Overview

AMS_TAR_ORGANIZATION_V is an APPS-owned database view within the Oracle E-Business Suite Marketing (AMS) module. It exposes the organization-specific records held for list source types categorized as "AMS_ORGANIZATION", filtering the underlying list entry data so that only rows associated with that particular source system type are visible. In practice, the view provides a simplified, denormalized presentation of organization marketing list entries, translating generic slot columns (COL1 through COL6) into meaningful business attributes.

This view plays a targeted role in Oracle EBS reporting and integration. Because it pre-filters on LIST_ENTRY_SOURCE_SYSTEM_TYPE = 'AMS_ORGANIZATION' and renames positional columns into semantic ones, it is commonly used by Marketing list management reports, third-party campaign tools, and customer data integration jobs that need to consume organization-level list members without navigating the full AMS_LIST_ENTRIES table. It is valid in EBS 12.1.1 and 12.2.2 and is owned by the APPS schema.

Underlying Base Objects

The view is defined over a single referenced base object, AMS_LIST_ENTRIES, accessed through a synonym. The defining query selects a specific subset of columns and applies the predicate WHERE LIST_ENTRY_SOURCE_SYSTEM_TYPE = 'AMS_ORGANIZATION'. This means AMS_TAR_ORGANIZATION_V is effectively a filtered projection of AMS_LIST_ENTRIES, not a join across multiple tables. Columns such as COL1, COL2, COL3, COL4, COL5, and COL6 in the base table are mapped to SIC_CODE, CUSTOMER_KEY, TAX_REFERENCE, JGZZ_FISCAL_CODE, DUNS_NUMBER, and TAX_NAME respectively, reflecting the organization data model of the TCA (Trading Community Architecture) party records associated with each list entry.

Key Columns

For users searching on "customer_key", the relevant column is CUSTOMER_KEY, which is the renamed COL2 from the base table. It represents the customer identifier used for the organization list entry and is the primary lookup field for customer-based filtering and joins.

• CUSTOMER_KEY — Customer identifier (base COL2); central to customer-driven queries.
• ORGANIZATION_NAME — The organization's display name (CUSTOMER_NAME).
• PARTY_ID — TCA party identifier linking to HZ_PARTIES.
• DUNS_NUMBER — Organization D-U-N-S number (base COL5).
• JGZZ_FISCAL_CODE — Fiscal code (base COL4).
• SIC_CODE — Standard Industrial Classification code (base COL1).
• TAX_NAME / TAX_REFERENCE — Tax-related attributes (base COL6 / COL3).
• LIST_ENTRY_ID / LIST_HEADER_ID — Keys tying the entry to its parent marketing list.
• LIST_ENTRY_SOURCE_SYSTEM_TYPE — Filter attribute, always 'AMS_ORGANIZATION'.
• CAMPAIGN_ID, MEDIA_ID, CHANNEL_ID, EVENT_OFFER_ID — Marketing context identifiers.
• ENABLED_FLAG, MANUALLY_ENTERED_FLAG, MARKED_AS_DUPLICATE_FLAG — Status and quality flags.
• Audit columns — CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY, OBJECT_VERSION_NUMBER.

Common Use Cases and Queries

The view is typically used to retrieve organization list members, resolve a customer_key to a party, or export list entry data for campaign execution. A basic lookup joining the list header is common.

• Retrieve an organization entry by customer key:

  SELECT customer_key, organization_name, party_id
  FROM   apps.ams_tar_organization_v
  WHERE  customer_key = :p_customer_key;

• Report all enabled organizations on a given list:

  SELECT list_header_id, organization_name, duns_number
  FROM   apps.ams_tar_organization_v
  WHERE  enabled_flag = 'Y'
  AND    list_header_id = :p_list_header_id;

• Link list entries to TCA party details:

  SELECT v.customer_key, v.organization_name, p.party_name
  FROM   apps.ams_tar_organization_v v,
         apps.hz_parties p
  WHERE  v.party_id = p.party_id;

Because the view applies a fixed source-system filter, queries do not need to add the LIST_ENTRY_SOURCE_SYSTEM_TYPE predicate, simplifying reporting logic and reducing the risk of inadvertently including non-organization list entries.