Results for “fk_key”

12 results




Overview
Explore in AI Chat

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

Overview

The APPS.OPI_EDW_JOB_DETAIL_F_SZ package body is a source-side extraction routine that belongs to the Oracle Process Manufacturing / Discrete Manufacturing Enterprise Data Warehouse (EDW) staging layer. The suffix _SZ denotes a "size" package: its role is not to move data, but to provide the Oracle E-Business Suite data warehouse framework with the two critical measurements it requires before extracting a fact table — the number of rows that qualify for the current load window, and an estimate of the byte width of each row. These two values allow the EDW collection engine to pre-allocate storage, size the staging buffer, and report progress to the concurrent manager. The package targets discrete and repetitive job detail facts sourced from Work in Process (WIP) tables and is registered under the APPS schema as an API classification of OTHER. It is deployed against EBS 12.1.1 and 12.2.2 and is referenced by one other package in the same EDW family.

Key Procedures and Functions

The package exposes two documented procedures, both accepting a p_from_date and p_to_date date range that represents the incremental load window used by the EDW collection program.

  • CNT_ROWS — Returns, through an OUT parameter, the total number of fact rows that satisfy the date window. The body implements this using a SUM over a UNION of three COUNT(*) queries, each anchored on WIP_ENTITIES and joined to a specific job type: WIP_DISCRETE_JOBS, WIP_REPETITIVE_SCHEDULES, and WIP_FLOW_SCHEDULES. Only jobs in the released/complete/closed status set (STATUS_TYPE 4, 5, 7, 12, or STATUS = 2 for flow schedules) are counted, and each query is filtered by EN.LAST_UPDATE_DATE between the supplied bounds.
  • EST_ROW_LEN — Returns, through an OUT parameter, an estimated row length in bytes for the same logical fact row. Internally it declares a set of scalar variables whose names mirror the target fact columns (job number, job ID, organization, item, entity type, creation and update dates, routing information, actual and planned quantities, planned and actual start/complete/cancel dates, production line FK and routing revision FK, plus the equivalent repetitive-schedule column set). This naming mirrors the "fk_key" concept the user searched for: the FK-suffixed variables (x_PRD_LINE_FK_DI, and the parallel repetitive/flow variants) represent foreign key columns measured for size, confirming that production line and routing revision are carried into the fact as dimensional foreign keys.

Tables Accessed

All access is read-only against APPS synonyms. The fact is anchored on WIP_ENTITIES, joined to WIP_DISCRETE_JOBS, WIP_REPETITIVE_SCHEDULES, and WIP_FLOW_SCHEDULES to capture the three job varieties. Supporting transactional and dimensional tables include MTL_SYSTEM_ITEMS and MTL_PARAMETERS for item and organization context, MTL_MATERIAL_TRANSACTIONS and WIP_MOVE_TRANSACTIONS for material movement facts, WIP_PERIOD_BALANCES for period values, and the organization structures HR_ALL_ORGANIZATION_UNITS, HR_ORGANIZATION_INFORMATION, and EDW_LOCAL_INSTANCE to resolve operating unit and warehouse relationships.

Usage Notes

This package is not invoked interactively. The EDW collection framework — typically the "EDW Collector" or a data warehouse load concurrent program — calls CNT_ROWS first to size the extraction, then calls EST_ROW_LEN to determine staging width before the actual fact cursor is opened. Because both procedures rely solely on the date window and organization security predicates, custom code should only invoke them within an EDW load context; they perform no DML and are safe to call repeatedly. Any modification to job status filters or the foreign-key columns carried into the fact must be reflected in both procedures to keep sizing and extraction consistent.