Search Results get_unit_cost

Overview

OPI_DBI_INV_CCA_OPM_PKG is an Oracle Applications (APPS) PL/SQL package that supports the Cycle Count Accuracy (CCA) extract, transform, and load (ETL) process for Oracle Process Manufacturing (OPM) environments. Cycle count accuracy measures how closely physical inventory counts match system inventory records; the package supplies the process manufacturing data collection and calculation logic that feeds the Oracle Daily Business Intelligence (DBI) inventory analytics layer. Its header revision ($Header: OPIDEICCAPS.pls 120.1) dates to 2005, consistent with the DBI/OPM lineage that carried forward into EBS 12.1.1 and 12.2.2.

The package is classified as OTHER in ETRM and exposes a deliberately narrow public interface: a single initial-load entry point and a single cost-derivation function. Internal logic is driven by package-level constants that distinguish pre-R12 source data (C_PRER12_SOURCE := 3) and tag OPM CCA rows through markers such as 'OPMCCA' and 'CCA', along with the package name and error buffer size used for concurrent program error reporting.

Key Procedures and Functions

• RUN_INITIAL_LOAD_OPM — The wrapper routine that performs the initial load of the Cycle Count Accuracy ETL. It follows the standard concurrent program calling convention, accepting errbuf and retcode as IN OUT NOCOPY parameters so that completion status (-1 for error, 0 for success, 1 for warning) and any diagnostic message are returned to the concurrent manager. It is the entry point used to seed the CCA staging and reporting structures from OPM cycle count data.
• GET_UNIT_COST — A wrapper function around the OPM cost routine gmf_cmcomman.unit_cost, declared with PARALLEL_ENABLE so it can be invoked safely from parallel DML/query operations during the ETL. It accepts an item identifier, warehouse code, organization code, and creation date, and returns the unit cost as a NUMBER. The creation date parameter allows the cost lookup to be resolved as of a point in time, which is important for historical cycle count accuracy reporting. The get_unit_cost function is the object most commonly targeted when users search for unit cost derivation within this package.

Tables Accessed

The documented table references span both OPM base tables and Oracle Discrete/Common inventory structures, reflecting a consolidation of cycle count data across sources:

• IC_CYCL_HDR, IC_CYCL_DTL, IC_CYCL_ADT — OPM cycle count header, detail, and audit tables; the primary source of count quantities and variance data.
• IC_ITEM_MST_B, IC_WHSE_MST, IC_WHSE_INV, IC_RANK_MST — OPM item master, warehouse master, warehouse inventory balances, and cycle count rank definitions used to classify items and resolve current on-hand quantities.
• IC_TRAN_CMP — OPM inventory transaction completion data, used to reconcile movements against count results.
• MTL_SYSTEM_ITEMS_B — The common item master, used to join OPM items to the shared inventory item definitions.
• GL_SUBR_LED — Subledger accounting data, referenced for cost and valuation alignment in the accuracy calculations.
• OPI_DBI_INV_CCA_STG — The DBI staging table that receives the transformed cycle count accuracy rows produced by the initial load.

Usage Notes

This package is not a form-driven API; it is designed for batch invocation. RUN_INITIAL_LOAD_OPM is registered with a concurrent program so that the CCA initial load can be scheduled through the standard request submission framework, with errbuf and retcode consumed by the concurrent manager. GET_UNIT_COST is invoked internally by the ETL and may be called from custom PL/SQL or parallelized SQL that requires OPM unit cost resolution; its PARALLEL_ENABLE pragma is specifically intended to permit use in parallel execution. The package is referenced by one other documented package, indicating it forms part of a larger DBI ETL dependency chain. Because OPI_DBI objects were superseded by later BI Publisher and OBIA-based analytics, use is largely confined to environments that retain the original DBI inventory dashboards. All execution should be performed as the APPS schema or through a synonym granted to the calling schema.