Search Results identify_change

Overview

The APPS.OPI_EDW_COGS_F_C package body is a component of the Oracle E-Business Suite Operational Intelligence (OPI) Enterprise Data Warehouse (EDW) extract infrastructure. Its principal purpose is to identify and propagate incremental changes to sales order, invoicing, and inventory transaction data so that the Cost of Goods Sold (COGS) fact can be refreshed in the EDW — in both centralized and distributed (data warehouse) deployment topologies. The package supports the OPI EDW COGS fact load process and provides the programmatic interface used by the OPI family of concurrent programs to push COGS-relevant data.

The source header indicates a creation lineage dating to 2003, with a subsequent revision in May 2006 delivered under the OPIMCOGB.pls script. The package carries a documented classification of OTHER within the ETRM repository. Its name suffix _F_C follows the EDW naming standard for a fact-table package body, complementing the staging and incremental tracking objects described below.

Key Procedures and Functions

The package exposes two documented entry points:

• PUSH — Triggers the extraction and propagation of COGS-relevant transactional data from the local instance. Internally, the package uses helper functions such as LOCAL_SAME_AS_REMOTE (which compares the local instance code from EDW_LOCAL_INSTANCE with the remote instance code fetched over the edw_apps_to_wh database link) and IDENTIFY_CHANGE, which determines changed rows by evaluating last_update_date values and inserting qualifying identifiers into OPI_EDW_COGS_INC. The IDENTIFY_CHANGE routine carries an explicit annotation dated 03-Nov-2003 concerning an additional predicate added to correctly identify logical transactions in a Drop Ship scenario (11.5.10 impact analysis).
• PUSH_TO_LOCAL — Directs the COGS data propagation toward the local instance rather than, or in addition to, the remote warehouse, allowing deployments where the source and target reside in the same database.

Package-level globals include g_push_from_date, g_push_to_date, g_row_count, g_exception_msg, g_errbuf, and g_retcode, which are used to control the extraction window and to capture error information in the standard concurrent-program buffer variables.

Tables Accessed

The package reads and writes the following documented objects (accessed through APPS synonyms):

• OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL — Source sales order header and line data used to correlate inventory transactions with order demand, including drop-ship flows.
• MTL_MATERIAL_TRANSACTIONS, MTL_TRANSACTION_ACCOUNTS — Inventory transactions and their accounting distributions, the primary cost source for COGS.
• MTL_PARAMETERS — Organization-level inventory setup used to constrain the extraction scope to valid inventory organizations.
• RA_CUSTOMER_TRX_LINES_ALL, AP_INVOICES_ALL, AP_INVOICE_DISTRIBUTIONS_ALL — Receivables and Payables line and distribution data supporting COGS reconciliation.
• EDW_LOCAL_INSTANCE, DUAL — Instance identification and sequence/utility operations.
• OPI_EDW_COGS_INC, OPI_EDW_COGS_INC_S, OPI_EDW_COGS_FSTG — The incremental tracking table, its sequence, and the COGS staging table populated for downstream fact loading.

Usage Notes

This package body is invoked by OPI EDW concurrent programs responsible for refreshing the COGS fact, and its procedures are executed during scheduled warehouse load cycles rather than interactively from forms. It is referenced by one other package in the ETRM repository, indicating a dependent load or orchestration component. The g_errbuf and g_retcode conventions confirm that PUSH and PUSH_TO_LOCAL are designed to be called as concurrent program executables, with parameters governing the from/to date window. Because the package relies on a database link (edw_apps_to_wh) and on the EDW_LOCAL_INSTANCE configuration, correct setup of the EDW instance metadata is a prerequisite. Customers searching for cogs_pk should note that COGS-related logic in EBS is dispersed across the OPI EDW packages; this package body governs the extraction and staging layer rather than any single primary-key constraint, and its behavior is governed by the last_update_date-based incremental mechanism described above.