Results for “fnd_dm_products”

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

Overview

CSP_PRODUCT_POPULATIONS is a table in the CSP (Spares Management) product schema of Oracle E-Business Suite, documented as VALID in both 12.1.1 and 12.2.2. Its stated purpose is to store the population of a product — that is, the installed base quantity of a given product at a given planning location or organization, expressed through the CURRENT_POPULATION and POPULATION_CHANGE columns. In the spares planning model, population drives demand forecasting and repair/item stocking decisions, so this table forms the quantitative foundation that CSP planning parameters operate against.

From a Data Vault modeling perspective, the mined foreign-key structure classifies this table as satellite-leaning. It carries descriptive, time-stamped measurements (population counts and deltas) plus a set of foreign keys pointing to parent entities, which is characteristic of a satellite attached to a business key. This is offered as a heuristic modeling suggestion, not as a physical implementation statement about EBS itself.

Key Information Stored

The table documents 11 columns. The most significant are:

• PRODUCT_POPULATION_ID — the surrogate primary key, defined by CSP_PRODUCT_POPULATIONS_PK. It is also the sole column of unique index CSP_PRODUCT_POPULATIONS_U1.
• PRODUCT_ID — identifies the product whose population is recorded; a foreign key to FND_DM_PRODUCTS.
• PLANNING_PARAMETERS_ID — the planning parameter set under which the population applies; a foreign key to CSP_PLANNING_PARAMETERS.
• CURRENT_POPULATION — the current recorded population quantity for the product.
• POPULATION_CHANGE — the delta or change applied to the population, supporting historical or incremental tracking.
• SECURITY_GROUP_ID — the security group scoping the row; a foreign key to FND_SECURITY_GROUPS.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — the standard EBS auditing (WHO) columns.

Business-key candidates are captured by the unique indexes: U1 on PRODUCT_POPULATION_ID alone (effectively the surrogate), and U2 on the composite (PRODUCT_ID, PLANNING_PARAMETERS_ID). The U2 constraint implies that population is recorded once per product per planning parameter set, making that pair the natural business key. The surrogate PRODUCT_POPULATION_ID remains the enforced PK.

Common Use Cases and Queries

Typical scenarios include validating population records for a product, extracting current population for forecasting feeds, and auditing incremental population changes. A representative query joining to the planning parameters parent is:

• SELECT pp.product_id, pp.current_population, pp.population_change FROM csp.csp_product_populations pp WHERE pp.product_id = :product_id;
• SELECT pp.*, cp.* FROM csp.csp_product_populations pp JOIN csp.csp_planning_parameters cp ON pp.planning_parameters_id = cp.planning_parameters_id WHERE pp.security_group_id = :sec_group;
• Aggregating population by product for installed-base reporting: SELECT product_id, SUM(current_population) FROM csp.csp_product_populations GROUP BY product_id;
• Change-tracking: filtering on LAST_UPDATE_DATE between an audit window to identify recently modified populations.

Because security is row-scoped by SECURITY_GROUP_ID, multi-org and multi-security reporting should always constrain or join on that column.

Related Objects

• CSP_PLANNING_PARAMETERS — parent of PLANNING_PARAMETERS_ID; the primary join for planning context.
• FND_DM_PRODUCTS — parent of PRODUCT_ID; provides product master attributes.
• FND_SECURITY_GROUPS — parent of SECURITY_GROUP_ID; governs row-level access.
• CSP_PRODUCT_POPULATIONS_PK / _U1 / _U2 — the indexes enforcing uniqueness on the surrogate and the (PRODUCT_ID, PLANNING_PARAMETERS_ID) business key.

These form the immediate dependency set; downstream CSP planning and forecasting processes consume the population figures stored here.