Search Results get_shift_info

Overview

WIP_WS_EMBEDDED_ANALYTICS_PK is an Oracle EBS Work in Process (WIP) package that supplies the analytical engine behind the Manufacturing Execution System (MES) First Pass Yield (FPY) and Parts Per Million (PPM) Defects functionality. As stated in its package header, the module exists to compute first pass yield and defect concentration metrics at the job, operation, department, and assembly levels, partitioned by day, week, and shift. The package is declared AUTHID CURRENT_USER under the APPS schema and carries a header revision stamp of 120.5 (2008/03/18), placing it within the 12.1.1 code line and carried forward into 12.2.2 without structural change. It is classified in ETRM as an OTHER API rather than a public interface, meaning it is intended for internal consumption by the MES dashboard and concurrent processing layers rather than by customer extension code.

Key Procedures and Functions

The package exposes 36 documented program units. The function most directly associated with shift qualification is get_shift_info, which accepts an organization identifier, a department identifier, and a transaction date, and returns a numeric shift value. It is the primitive that the remainder of the package relies upon to attribute a transaction timestamp to the correct shift bucket for a given department.

The data assembly stage is handled by populate_fpy_raw_data, which materializes raw yield and defect rows for an execution date range and organization. All remaining units are calculation routines that aggregate that raw data into the WIP_WS_FPY tables. They fall into families:

• Job/Operation level: calc_fpy_per_jobop_day, calc_fpy_per_jobop_day_shift, calc_fpy_per_jobop_week, calc_fpy_per_jobop_week_shift.
• Job level: calc_fpy_per_job_day, calc_fpy_per_job_day_shift, calc_fpy_per_job_week, calc_fpy_per_job_week_shift.
• Department level: calc_fpy_per_dept_day, calc_fpy_per_dept_day_shift, calc_fpy_per_dept_week, calc_fpy_per_dept_week_shift.
• Aggregate department level: calc_fpy_all_depts_day, calc_fpy_all_depts_day_shift, calc_fpy_all_depts_week, calc_fpy_all_depts_week_shift.
• Assembly level: calc_fpy_per_assm_day, calc_fpy_per_assm_day_shift, and their weekly counterparts.

Each calculation routine follows the same contract: an execution date, a cutoff date, an organization identifier, and an OUT NOCOPY return status. The shared globals g_user_id, g_login_id, g_request_id, and g_logLevel indicate the package participates in standard EBS concurrent request logging and FND_LOG instrumentation.

Tables Accessed

The package reads from and writes to the following APPS synonyms:

• WIP_DISCRETE_JOBS — supplies job identity and assembly context for the per-job and per-assembly rollups.
• WIP_OPERATIONS — provides the operation sequence used to build job/operation yield denominators.
• WIP_MOVE_TRANSACTIONS — the transactional source from which completed and scrapped quantities, and their timestamps, are drawn for yield and shift attribution.
• WIP_WS_FPY — the destination table where computed first pass yield results are stored for dashboard consumption.
• WIP_WS_PPM_DEFECTS — the destination for defect PPM aggregation, completing the MES quality metrics pair.

Usage Notes

WIP_WS_EMBEDDED_ANALYTICS_PK is invoked by the MES embedded analytics concurrent programs, which call populate_fpy_raw_data followed by the appropriate calculation routines for the requested reporting grain and period. The package is referenced by zero other PL/SQL packages per ETRM metadata, confirming it sits at the top of its call chain rather than serving as a shared utility. Custom code should not call the calculation entry points directly, because they assume raw data has already been populated for the same organization and date window. Where custom reporting requires only shift attribution, get_shift_info is the safe, side-effect-free point of entry, since it performs a lookup and returns a value without touching WIP_WS_FPY or WIP_WS_PPM_DEFECTS.