Results for “ams_dm_bic_stg”

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

Overview

AMS_DM_BIC_STG is a staging table in the AMS (Marketing) schema of Oracle E-Business Suite, valid in both release 12.1.1 and 12.2.2. It serves as the container for customer intelligence specific details associated with a party, holding supplemental attributes for rows that originate in AMS_DM_DRV_STG. The table is populated and consumed by the mining collection pack, a component of the Oracle Marketing customer intelligence and data mining infrastructure that aggregates behavioral, transactional, and service-related metrics for use in segmentation, scoring, and predictive modeling. As a staging object, it is transient by design: data is loaded, transformed, and then propagated into downstream mining and analytical structures, after which the staged rows may be purged or refreshed on the next collection cycle.

The documented physical schema in ETRM 12.2.2 records the owner as AMS with 60 columns. The primary key is AMS_DM_BIC_STG_PK, defined on PARTY_ID, and a unique index AMS_DM_BIC_STG_U1 also exists on PARTY_ID, confirming that one row represents exactly one party. Because PARTY_ID is both the primary key and the sole documented unique key, the table is effectively a party-keyed attribute container. Mined from the foreign key structure, the heuristic Data Vault classification is standalone; this is a modeling suggestion only, reflecting the absence of enforced foreign keys, and it would most naturally be modeled as a satellite keyed by the party business key rather than as a hub or link.

Key Information Stored

The 60 documented columns fall into recognizable subject areas. The most significant include:

• PARTY_ID — the surrogate primary key and business-key candidate (unique index AMS_DM_BIC_STG_U1); identifies the party to which all metrics belong.
• AVG_ORDER_AMOUNT, AVG_UNITS_PER_ORDER — average order value and average line quantity, core purchase-behavior measures.
• TOT_ORDER_AMOUNT_YEAR, TOT_ORDER_AMOUNT_3_MONTHS, TOT_ORDER_AMOUNT_6_MONTHS, TOT_ORDER_AMOUNT_9_MONTHS — trailing revenue windows used for trend and recency analysis.
• TOT_NUM_ORDERS_YEAR, TOT_NUM_ORDER_3_MONTHS, TOT_NUM_ORDER_6_MONTHS, TOT_NUM_ORDER_9_MONTHS — corresponding order-frequency counts; note the documented inconsistency in naming (ORDERS versus ORDER).
• ORDER_RECENCY, ORDER_QTY_CUMUL, ORDER_LINES_DELIVERED, ORDER_LINES_ONTIME, ONTIME_SHIP_PCT — fulfillment and recency indicators.
• NUM_OF_SR_YEAR, NUM_OF_SR_6_MONTHS, NUM_OF_SR_3_MONTHS, NUM_OF_SR_1_MONTH, OPEN_SRS, CLOSED_SRS, ESCALATED_SRS, REOPENED_SRS — service-request volume and lifecycle measures.
• AVG_RESOLVE_DAYS_YEAR, AVG_RESOLVE_DAYS_6_MONTHS, AVG_RESOLVE_DAYS_3_MONTHS, AVG_RESOLVE_DAYS_1_MONTH — resolution timeliness by window.
• CALLS-related metrics: TOT_CALLS, CALL_LENGTH, AVG_TALK_TIME, AVG_HOLD_TIME, FIRST_CALL_CL_RATE, PCT_CALL_REWORK, TOTAL_SR_RESPONSE_TIME — interaction-center efficiency and quality.
• CONTRACTS_CUML, CONTRACT_AMT, CONTRACT_DURATION, NEW_CONTRACTS, RENEWED_CONTRACTS, OPEN_CONTRACTS, INACTIVE_CONTRACTS — contract portfolio and renewal activity.
• PAYMENTS, ONTIME_PAYMENTS, RETURNS, RETURN_BY_VALUE, RETURN_BY_VALUE_PCT, SALES, COGS, PROFITABILITY — financial contribution and risk indicators.

The remaining columns (for example NUM_OF_COMPLAINTS, AVG_COMPLAINTS, NUM_OF_INTERACTIONS, REFERALS, PRODUCTS, AVG_WORKLOAD, AVG_LEN_OF_EMP) complete the customer intelligence profile.

Common Use Cases and Queries

Typical usage centers on high-value party selection and mining model input. A simple profile lookup is:

• SELECT party_id, tot_order_amount_year, tot_num_orders_year, profitability FROM ams.ams_dm_bic_stg WHERE party_id = :p_party_id;
• Windowed revenue comparison for upsell targeting: SELECT party_id, tot_order_amount_3_months, tot_order_amount_6_months, tot_order_amount_year FROM ams.ams_dm_bic_stg WHERE tot_order_amount_3_months > 0 ORDER BY tot_order_amount_3_months DESC;
• Service-quality segmentation: filter on pct_first_closed_srs, escalated_srs, and avg_resolve_days_3_months to isolate parties needing intervention.
• Joining to the driver staging table on party_id to combine base driver attributes with these intelligence metrics before loading a mining build.

Because the table is a staging object, queries should assume periodic truncation or reload and should not be used as a system of record.

Related Objects

• AMS_DM_DRV_STG — the parent staging table whose rows AMS_DM_BIC_STG supplements; join on PARTY_ID.
• HZ_PARTIES — the party master, joined on PARTY_ID to resolve party names and identifiers.
• AMS_DM_BIC_STG_PK / AMS_DM_BIC_STG_U1 — the primary key constraint and unique index on PARTY_ID, enforcing one row per party.
• Mining collection pack programs — the concurrent/collection programs that populate and consume the staging data during a mining run.
• Downstream AMS customer intelligence and mining result tables — targets of the transformed staging data used in segmentation and scoring.