Search Results edw_util




Overview
Explore in AI Chat

Overview

APPS.EDW_UTIL is a utility PL/SQL package belonging to the Oracle E-Business Suite Enterprise Data Warehouse (EDW) and Business Intelligence (BI) product family. Its classification in the ETRM metadata is UTIL, indicating that it does not implement a transactional business process of its own; rather, it centralizes common lookup and conversion logic required by the EDW materialized views, fact collection views, and reporting programs that populate the Oracle Business Intelligence Applications (OBIA/OSA) data model.

The package resolves frequently needed reference values — base currency, unit of measure, cost, transaction value, organization and party identifiers — so that the collection and fact view layer does not have to repeat this logic in every object. In EBS 12.1.1 and 12.2.2 it resides in the APPS schema, has a status of VALID, and is documented in the ETRM repository with 19 procedures and functions.

Key Procedures and Functions

The documented API surface covers five broad areas of utility logic:

GET_BASE_CURRENCY and GET_BASE_TRANSACTION_VALUE provide the currency context and converted transaction amount used by financial fact views such as the FII accounts payable, accounts receivable, and projects cost views. GET_WH_GLOBAL_CURRENCY returns the warehouse-level currency used by global reporting. GET_ITEM_COST returns the cost of an item, supporting cost-of-goods-sold and inventory valuation facts (OPI_EDW_COGS_FCV, OPI_EDW_INV_DAILY_STAT_F_C). The UOM group normalizes item quantities into EDW base units when source transactions are recorded in differing units. GET_PTO_MMT_COUNT and GET_REP_SCHED_SCRAPPED_QTY support manufacturing and repair quantity reporting. GET_EST_SHIP_DATE derives estimated ship dates for order and backlog facts. GET_PARTY_ID resolves trading partner or customer identity, GET_LINE_DETAIL_COUNT returns detail line counts, and BUS_UNIT_ID and GET_APP_INFO expose business unit and application environment context.

Tables Accessed

EDW_UTIL reads reference and transactional data through APPS synonyms. Costing logic draws on CST_COST_TYPES and CST_ITEM_COSTS. Currency and warehouse configuration come from FND_PRODUCT_GROUPS, EDW_LOCAL_INSTANCE, EDW_LOCAL_SYSTEM_PARAMETERS, and MTL_PARAMETERS. Unit-of-measure definitions are held in EDW_MTL_LOCAL_UOM_M. Organization and party identity are resolved through HR_ALL_ORGANIZATION_UNITS, HR_ORGANIZATION_INFORMATION, and HZ_CUST_ACCOUNTS. Order and transaction detail comes from OE_ORDER_LINES_ALL, MTL_TRANSACTION_ACCOUNTS, and MRP_FULL_PEGGING, MRP_GROSS_REQUIREMENTS, and MRP_RECOMMENDATIONS for planning-related quantities. The package itself only references SYS.STANDARD at the object level, confirming that it is a pure query/utility package rather than a DML driver.

Usage Notes

EDW_UTIL is not invoked from EBS forms or standard concurrent programs. It is called internally by the EDW fact collection programs and view definitions, which is evidenced by the 33 dependent objects listed in the ETRM metadata, including FIIBV_AP_INV_LINES_FCV, FII_AR_TRX_DIST_FCV, ISCBV_BACKLOGS_FCV, ISCBV_BOOKINGS_FCV, OPI_EDW_COGS_FCV, OPI_EDW_RES_UTIL_FCV, POA_EDW_AGREEMENT_LINES_FCV, and POA_EDW_VARIABLES_PKG. During ETL and data warehouse collection runs, these dependent packages and views call EDW_UTIL to obtain consistent conversion factors, cost values, and currency codes. Because it is a shared utility, any customization or patching of EDW_UTIL should be treated as high-impact: a change can affect every downstream fact and collection object that references it. Oracle therefore supports EDW_UTIL as part of the standard EDW/BI code line, and customized logic is normally placed in separate, user-owned packages rather than modifying APPS.EDW_UTIL directly.