Search Results clean_staging_table

Overview

MSD_PULL_FACT_DATA is an Oracle E-Business Suite PL/SQL package body owned by the APPS schema and classified under the ETRM metadata as an "OTHER" API type. It belongs to the MSD (Marketing and Sales Data) product family, which supplies the ETRM/Demantra-family analytical and data-collection infrastructure. The package implements the fact-extraction layer of the MSD staging architecture: it reads rows from MSD staging tables (the MSD_ST_* synonym family) and moves them into the corresponding fact tables that feed downstream reporting and analytics. It is an orchestration package rather than a single-purpose utility — the public PULL_FACT_DATA procedure sequentially drives a set of private "pull" procedures, each responsible for one subject area such as shipments, bookings, forecasts, or pricing.

The metadata records the same source for both Oracle EBS 12.1.1 and 12.2.2, with the header revision 120.3 dated 2005, indicating the object has been stable across releases and is not an Online Patching–affected artifact in the usual sense.

Key Procedures and Functions

The ETRM documentation lists ten procedures or functions. The entry point is PULL_FACT_DATA, which takes only the standard concurrent-program errbuf and retcode OUT parameters. It initializes retcode to zero and then calls, in order, PULL_SHIPMENT_DATA, PULL_BOOKING_DATA, PULL_UOM_CONVERSION, PULL_CURRENCY_CONVERSION, PULL_MFG_FORECAST, and PULL_PRICING_DATA. A WHEN OTHERS handler truncates SQLERRM to 150 characters into errbuf and sets retcode to -1.

Several documented procedures are commented out of the main flow: PULL_OPPORTUNITIES_DATA and PULL_SALES_FORECAST appear as declarations but their invocations are disabled in the orchestration body. PULL_EVENTS is also documented but is not called from PULL_FACT_DATA, implying separate invocation. Each pull procedure follows the same signature convention: errbuf and retcode OUT NOCOPY parameters, often with a local x_delete_flag controlling whether the target fact table is purged before loading.

Two private cleanup procedures, CLEAN_STAGING_TABLE and CLEAN_PRICING_STAGING_TABLE, are declared. The user's search term "clean_staging_table" refers directly to Clean_Staging_Table, which accepts a table name, a date column, an instance ID, a from-date, a to-date, and a forecast designation. It removes obsolete staging rows before the fact pull executes, bounding the volume of data processed. CLEAN_PRICING_STAGING_TABLE performs the analogous operation for price-list staging, taking a table name, instance ID, and price list.

Package constants C_FROM_DATE (01-01-1000) and C_TO_DATE (01-01-4000) define the widest practical date range for staging filters.

Tables Accessed

All table access is through APPS synonyms. The documented staging tables are MSD_ST_BOOKING_DATA, MSD_ST_CURRENCY_CONVERSIONS, MSD_ST_MFG_FORECAST, MSD_ST_PRICE_LIST, MSD_ST_SHIPMENT_DATA, MSD_ST_TIME, and MSD_ST_UOM_CONVERSIONS. Each supports a matching pull routine: shipment staging feeds shipment facts, booking staging feeds booking facts, and so on. MSD_ST_TIME and DUAL are used for date and utility logic. CLEAN_STAGING_TABLE deletes rows from these staging tables based on the supplied date column and instance ID, while the pull procedures insert the surviving rows into the target fact tables (for example, the shipment fact table referenced as MSD_COMMON_UTILITIES.SHIPMENT_FACT_TABLE).

Usage Notes

MSD_PULL_FACT_DATA is designed for scheduler-driven invocation, typically as the implementation of a concurrent program in the MSD responsibility. Because PULL_FACT_DATA exposes the standard errbuf/retcode pair, it can be registered directly as a concurrent executable without a wrapper. It is documented as being referenced by one other package, indicating it may also be called programmatically as part of a larger data-refresh chain. The cleanup procedures are private, so clean_staging_table cannot be called from outside the package; its behavior is triggered indirectly by the pull routines, which pass x_delete_flag := 'Y' when purging is required.