Search Results pmi_inv_calendar_v




Overview
Explore in AI Chat

Overview

PMI_INV_CALENDAR_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the PMI (Process Manufacturing Intelligence) product family. It presents inventory calendar information for process manufacturing organizations, exposing fiscal year and period definitions in a denormalized, query-ready form. The view is documented as VALID and is available in both EBS 12.1.1 and 12.2.2, where the APPS-owned definition and column list are identical.

Its principal role is to supply period-based reference data to operational and analytical reporting. Rather than requiring a report to join the calendar header and detail tables itself — and to apply the period-offset logic needed to derive a period start date — the view resolves that logic once and publishes a single row per period. This makes it a convenient source for date-bucketing in inventory, production, and cost reporting within the process manufacturing domain.

Underlying Base Objects

The view is defined over two base objects, both referenced through APPS synonyms: IC_CLDR_HDR (the calendar header) and IC_CLDR_DTL (the calendar detail lines). IC_CLDR_HDR supplies organization code, fiscal year, calendar description, year-level status flags, and the begin date of the year. IC_CLDR_DTL supplies the individual period records, including period number, period identifier, period type and description, period end date, period-level status flags, and audit columns.

The join is performed on ORGN_CODE and FISCAL_YEAR across the header and both detail aliases. Deleted calendar headers (DELETE_MARK = 0 filter) are excluded. The detail table is joined to itself: alias I1 carries the current period, while alias I2 carries the immediately preceding period. The join condition accepts either the first period of the year (where I1.PERIOD = 1 and both aliases coincide) or any later period (where I2.PERIOD = I1.PERIOD - 1). This self-join is what allows the view to compute a period start date without a window function.

Key Columns

  • ORGN_CODE — the organization (warehouse/plant) to which the calendar belongs; the primary partitioning key for most queries.
  • FISCAL_YEAR — the fiscal year of the calendar definition.
  • PERIOD_ID — the unique identifier of the period record.
  • PERIOD — the period number within the fiscal year.
  • PERIOD_TYPE / PERIOD_DESC — classification and descriptive text for the period.
  • START_DATE — derived date on which the period begins. For period 1 it equals the header BEGIN_DATE; for all later periods it is the prior period's end date plus one day.
  • END_DATE — the period end date, taken directly from the detail record.
  • CLOSED_YEAR_IND / CURRENT_YEAR_IND — year-level status flags from the header.
  • CLOSED_PERIOD_IND / CURRENT_PERIOD_IND — period-level status flags indicating whether the period is closed or current.
  • CALENDAR_DESC — descriptive name of the calendar.
  • CREATION_DATE / LAST_UPDATE_DATE — audit columns inherited from the detail record.

Common Use Cases and Queries

Typical usages include resolving the open or current period for an organization, mapping transactions to period start and end dates for trend reporting, and validating that a given date falls within an open period. The example below lists the current calendar periods for a single organization.

SELECT period, period_desc, start_date, end_date, closed_period_ind
FROM apps.pmi_inv_calendar_v
WHERE orgn_code = :p_orgn_code
AND fiscal_year = :p_fiscal_year
ORDER BY period;

To retrieve the currently open period, filter on the current-period indicator:

SELECT orgn_code, fiscal_year, period, start_date, end_date
FROM apps.pmi_inv_calendar_v
WHERE current_period_ind = 'Y'
ORDER BY orgn_code;

Because START_DATE and END_DATE are fully materialized, the view is well suited to joining against transaction tables on a date-range predicate, or to generating period labels for aggregation. Consumers should note that only non-deleted calendars are visible, and that the derived START_DATE depends on the integrity of consecutive period numbering within each fiscal year.