Results for “source_status_code”

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

Overview

BIM_DIMV_SOURCE_LISTS is an APPS-owned database view within the Oracle E-Business Suite that exposes source list records used by the Marketing Intelligence (BIM) product family. In EBS 12.1.1 and 12.2.2 the object carries a status of VALID, though the BIM Marketing Intelligence module itself is classified as obsolete, meaning the view is retained for backward compatibility and historical reporting rather than for active functional use.

The view consolidates source-oriented list headers into a denormalized, reporting-friendly shape. It filters the underlying import list headers to only those records whose IMPORT_TYPE equals 'SOURCE', joins them to a user-status lookup, and then appends a synthetic row representing the reserved value type code -999 drawn from FND_LOOKUPS. This design makes the view suitable for LOV (List of Values) population, concurrent-program parameters, and ad hoc dimensional reporting where a "source list" must be selected or displayed alongside descriptive status information. Because it unites data from the AMS import tables with a status view, it abstracts the join logic that consumers would otherwise have to replicate.

Underlying Base Objects

The ETRM metadata documents three referenced base objects underlying the view:

• AMS_IMP_LIST_HEADERS_ALL (SYNONYM) — the primary table supplying import list header records, aliased as IHL. The _ALL suffix indicates it is an organization-partitioned table, which is why an ORG_ID column is available on the view.
• AMS_USER_STATUSES_VL (VIEW) — a translated (VL) status view aliased as AUS, joined on USER_STATUS_ID to resolve status code, name, and description.
• FND_LOOKUPS (VIEW) — the standard EBS lookup view, used here only to produce the single reserved row for lookup type BIM_VALUE_TYPE and lookup code -999, unioned with the main query.
• FND_GLOBAL (PACKAGE) — documented as a referenced object, typically invoked to supply the runtime organization context (ORG_ID) used by the multi-org security applied to the AMS table.

The join condition is IHL.USER_STATUS_ID = AUS.USER_STATUS_ID, combined with the filter IHL.IMPORT_TYPE = 'SOURCE'. The two result sets are combined with UNION ALL, and the view is declared WITH READ ONLY, enforcing that no data manipulation is possible through it.

Key Columns

The view exposes seventeen columns. Principal among them is SOURCE_ORG_ID, which maps directly to IHL.ORG_ID and identifies the operating unit or organization that owns the source list — this is the column most commonly referenced when filtering by organization, and it is the term the user searched for. Other notable columns include:

• SOURCE_LIST_ID — the header's primary identifier (from IMPORT_LIST_HEADER_ID).
• SOURCE_LIST_NAME and SOURCE_LIST_VERSION — the descriptive name and version of the list.
• SOURCE_STATUS_CODE, SOURCE_STATUS_DATE, SOURCE_STATUS_NAME, SOURCE_STATUS_DESCRIPTION — status attributes resolved through AMS_USER_STATUSES_VL.
• VENDOR_ID and PIN_ID — vendor and party identifiers associated with the list.
• IMPORT_LIST_TYPE_ID — the source list type (from LIST_SOURCE_TYPE_ID).
• DESCRIPTION, TRANSACTIONAL_COST, TRANSACTIONAL_CURRENCY_CODE, FUNCTIONAL_COST, FUNCTIONAL_CURRENCY_CODE, and CREATION_DATE — descriptive and cost/currency detail for the header.

The synthetic -999 row returns MEANING in the SOURCE_LIST_NAME position and NULLs elsewhere, providing a placeholder entry for value-type lookups.

Common Use Cases and Queries

Typical usage includes populating LOVs for source-list selection, either globally or restricted to a specific organization, and reporting on source list status and cost data. The following example filters by the organization column the user searched for:

SELECT source_list_id,
       source_list_name,
       source_list_version,
       source_status_name,
       source_org_id
FROM   apps.bim_dimv_source_lists
WHERE  source_org_id = :p_org_id
ORDER  BY source_list_name;

A second scenario lists all active source lists with their functional cost:

SELECT source_list_id,
       source_list_name,
       functional_cost,
       functional_currency_code
FROM   apps.bim_dimv_source_lists
WHERE  source_status_code = 'ACTIVE'
  AND  source_list_id <> -999;

Because the view is read-only and based on obsolete BIM objects, it is recommended that new development not depend on it; existing reports and parameters referencing BIM_DIMV_SOURCE_LISTS should be reviewed for migration to supported alternatives during upgrades between 12.1.1 and 12.2.2.