Results for “cst_edw_backlog_v”

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

Overview

The view CST_EDW_BACKLOG_V is an Oracle E-Business Suite database object that belongs to the Bills of Material (BOM) product family. It is an Enterprise Data Warehouse (EDW) extraction view, purpose-built to surface order backlog information for downstream analytical and reporting consumption rather than for transactional use within EBS forms or concurrent programs. The view aggregates open order line data across the order management, shipping, and inventory domains and reshapes it into a dimensional, star-schema-friendly structure.

The naming convention confirms this role: the CST_ prefix and _EDW_ token indicate a cost/backlog subject area view intended for the EDW staging layer, while the BACKLOG_V suffix identifies it as a virtual (view) object rather than a materialized table. Notably, the ETRM 12.2.2 metadata records this object as not implemented in the database for the documented instance, meaning it exists as delivered view text but was not deployed in that particular environment; its DDL is nonetheless available for reference and deployment.

The user search term "bill_bklg_mrg_t" corresponds to the legacy backlog merge table historically associated with the same backlog reporting subject area, and queries or reports that previously targeted that table are frequently re-pointed to CST_EDW_BACKLOG_V in EDW integrations.

Underlying Base Objects

The ETRM documentation records no explicitly listed base tables for this view, and the Referenced base objects field is empty. The view text itself, however, reveals the query joins a standard set of EBS Order Management and Inventory entities. These include the order header and line tables (aliased H and L), a line-detail table (LD), the order actions table providing ACTION_ID values such as 7 (which denotes a ship/shipping action), the territory and salesrep reference tables, and an instance/installation reference (INST). Because this is a view, no physical storage is allocated; it is defined entirely as a stored SQL statement over those joined objects. The dimensional keys are generated by calling EDW package functions such as EDW_DIM_FK.ITEM_ORG, EDW_DIM_FK.OPERATING_UNIT, EDW_DIM_FK.TRADING_PARTNER, EDW_DIM_FK.TRADING_PARTNER, EDW_DIM_FK.EMPLOYEE, EDW_DIM_FK.PROJECT, EDW_DIM_FK.TASK, and EDW_DIM_FK.BUS_UNIT, along with EDW_UOM_PKG.GET_BASE_UOM_CODE. Reports querying this view therefore depend on the availability and validity of these EDW packages.

Key Columns

• BACKLOG_PK — a synthesized primary key concatenating SYSDATE, organization, customer, sales channel, salesrep, ship-to and invoice-to sites, project, task, warehouse, demand class, currency, and item identifiers. This composite key provides row uniqueness in the extract.
• TERRITORY_FK — foreign key to the sales territory dimension (RT.NAME).
• DATE_BALANCE_FK — the balance date, populated as SYSDATE, aligning the snapshot to the extraction date.
• INSTANCE_FK — instance identifier sourced from BIS_EDW_INSTANCE.GET_CODE, truncated to 40 characters.
• TOP_MODEL_ITEM_FK and ITEM_ORG_FK — item/organization dimension keys derived from the planned (PL.INVENTORY_ITEM_ID) and actual (L.INVENTORY_ITEM_ID) items respectively.
• BASE_UOM_FK — base unit of measure resolved through EDW_UOM_PKG.GET_BASE_UOM_CODE.
• OPERATING_UNIT_FK, CUSTOMER_FK, SALES_CHANNEL_FK, PRIM_SALES_REP_FK, PROJECT_FK, TASK_FK, INV_ORG_FK, BILL_TO_SITE_FK, SHIP_TO_SITE_FK, DEMAND_CLASS_FK, and TRX_CURRENCY_FK — standard EDW dimension foreign keys for the operating unit, trading partner, sales channel, primary sales representative, project, task, inventory organization, bill-to and ship-to sites, demand class, and transaction currency.
• Measure columns — aggregated in transaction currency, including gross margin computed via EDW_UTIL.GET_ITEM_COST and the selling price, with the action filter DECODE(CA.ACTION_ID, 7, ...) restricting results to the relevant action.

Common Use Cases and Queries

This view supports backlog, open-order, revenue-recognition, and sales-territory analytics in the EDW. Typical queries filter on the balance date and join the foreign keys to conformed dimensions:

• Backlog value by customer and territory: SELECT CUSTOMER_FK, TERRITORY_FK, SUM(measure) FROM CST_EDW_BACKLOG_V WHERE DATE_BALANCE_FK = TRUNC(SYSDATE) GROUP BY CUSTOMER_FK, TERRITORY_FK;
• Backlog by ship-to site and item organization: SELECT SHIP_TO_SITE_FK, INV_ORG_FK, COUNT(*) FROM CST_EDW_BACKLOG_V GROUP BY SHIP_TO_SITE_FK, INV_ORG_FK;
• Reporting runs that historically read bill_bklg_mrg_t can be migrated to this view where the EDW extraction path is preferred.

Because the view references EDW package functions and instance values, deployments must ensure those dependencies exist before the view is used in production ETL.