Results for “sales_credit_amount”

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

Overview

BIL_BI_PIPELINE_STG is a staging table owned by the BIL schema within the Oracle E-Business Suite Sales Intelligence module (BIL). Its documented purpose is to hold staged sales open and pipeline amounts that feed the Sales Intelligence reporting and analytics layer. In EBS 12.1.1 and 12.2.2, the BIL product supports sales management reporting such as pipeline analysis, forecast rollup, win/loss tracking, and sales credit attribution. Because the table name carries the "_STG" suffix, it is intended as an interim landing area: source transactions from opportunity, lead, credit, and order-line systems are consolidated here before being transformed into the BIL reporting facts used by Sales Intelligence dashboards. The object is reported as VALID in the BIL schema with 19 columns in the documented 12.1.1 physical layout.

From a Data Vault modeling perspective, the metadata heuristically classifies BIL_BI_PIPELINE_STG as standalone. This suggests it is not a conformed hub or link in a Data Vault sense, but rather a staging or satellite-style structure where foreign keys to AS_LEADS_ALL and IGS_FI_CREDITS_ALL act as degenerate references rather than members of a shared business-key hub.

Key Information Stored

The table captures the descriptive and monetary attributes required to evaluate pipeline and forecast position. The most significant columns documented are:

• LEAD_ID — foreign key to AS_LEADS_ALL identifying the originating lead; a primary business reference for pipeline rows.
• CREDIT_ID — foreign key to IGS_FI_CREDITS_ALL identifying the sales credit record associated with the transaction.
• LINE_ID — line-level identifier tying the staged record back to an opportunity or order line.
• LEAD_NUMBER — the human-readable lead number, a likely business-key candidate alongside LEAD_ID.
• SNAP_DATE — the snapshot date, indicating this is a point-in-time extract; SNAP_DATE combined with the identifiers typically forms the unique key.
• EFFECTIVE_DATE — the effective date of the pipeline or forecast fact.
• SALES_GROUP_ID — the sales group to which the pipeline amount is attributed.
• SALESREP_ID — the sales representative associated with the opportunity or credit.
• PRODUCT_CATEGORY_ID and ITEM_ID — the product hierarchy and inventory item used for product-level pipeline analysis.
• ITEM_ORGANIZATION_ID — the inventory organization qualifying the item.
• TXN_CURRENCY, PRIM_CONVERSION_RATE, and CONVERSION_RATE_S — currency and conversion factors supporting multi-currency reporting.
• SALES_CREDIT_AMOUNT — the monetary credit amount, the core measure of the staging row.
• WIN_PROBABILITY, WIN_LOSS_INDICATOR, and OPP_OPEN_STATUS_FLAG — likelihood and status attributes used to compute weighted pipeline.
• FORECAST_ROLLUP_FLAG — indicates whether the row contributes to forecast rollup totals.

The documented columns do not expose a single surrogate primary key; uniqueness is most plausibly enforced by the combination of LEAD_ID, LINE_ID, CREDIT_ID, and SNAP_DATE.

Common Use Cases and Queries

Typical uses include pipeline value by sales group or representative, weighted forecast calculations, win/loss trend analysis, and conversion of staged amounts into the BIL reporting facts. A representative query joining the two documented foreign keys:

• Pipeline by representative: SELECT s.salesrep_id, SUM(s.sales_credit_amount) FROM bil.bil_bi_pipeline_stg s WHERE s.opp_open_status_flag = 'Y' GROUP BY s.salesrep_id.
• Lead detail enrichment: SELECT l.lead_number, s.snap_date, s.sales_credit_amount FROM bil.bil_bi_pipeline_stg s JOIN as_leads_all l ON s.lead_id = l.lead_id.
• Credit reconciliation: join CREDIT_ID to IGS_FI_CREDITS_ALL to compare staged amounts against the credit source.
• Weighted forecast: multiply SALES_CREDIT_AMOUNT by WIN_PROBABILITY, filtered on FORECAST_ROLLUP_FLAG.
• Snapshot comparison: pivot on SNAP_DATE to measure movement in pipeline between periods.

Related Objects

The documented relationship set is deliberately narrow, reflecting the standalone classification:

• AS_LEADS_ALL — joined on LEAD_ID; supplies lead and opportunity header detail.
• IGS_FI_CREDITS_ALL — joined on CREDIT_ID; supplies sales credit and split information.
• BIL Sales Intelligence reporting facts/views — downstream consumers populated from this staging table.
• AS_OPP_LINES / opportunity line tables — conceptually linked via LINE_ID for line-level pipeline detail (inferred, not documented as FK).
• Currency and conversion objects — referenced logically through TXN_CURRENCY and the conversion rate columns.

Because only two foreign keys are documented, direct reference material for this table is limited; reliance on AS_LEADS_ALL and IGS_FI_CREDITS_ALL, together with the snapshot date, provides the primary analytical joins.