Search Results l_date

Overview

APPS.POA_DBI_CALENDAR_PKG is a PL/SQL package body in the Oracle E-Business Suite Procurement (POA) module, classified in ETRM as "OTHER." Its purpose is to resolve date boundaries used by Procurement Management dashboards and analytical reports (the DBI prefix indicating "Daily Business Intelligence"). The package translates a caller-supplied "as of" date, a period type (YTD, QTD, or MTD), and a comparison type into a concrete starting date for the reporting window. It performs no transactional work; it is a date-arithmetic utility that interprets the enterprise calendar hierarchy stored in the FII_TIME_* tables and returns a DATE to the calling report.

In EBS 12.1.1 and 12.2.2 the calendar used is the enterprise time dimension shipped with the FII (Financial Intelligence) schema. The package treats that calendar as the authoritative source of fiscal structure, so period-over-period comparisons honor the organization's defined year, quarter, period, and week boundaries rather than a fixed Gregorian model. This makes the package essential to consistent trend reporting.

Key Procedures and Functions

The package exposes five documented procedures and functions:

• CURRENT_REPORT_START_DATE — Returns the start date of the current reporting window for the supplied as-of date and period type, applying the comparison rule requested.
• PREVIOUS_REPORT_START_DATE — Returns the start date of the prior reporting window, used to align the comparative column against the current window.
• PREVIOUS_PERIOD_START_DATE — Returns the start date of the immediately preceding period (period, quarter, or year depending on the requested granularity).
• CURRENT_PERIOD_START_DATE — Returns the start date of the period containing the as-of date.
• PREVIOUS_PERIOD_ASOF_DATE — Returns the as-of date that maps to the equivalent point in the previous period, preserving alignment for ratio and variance calculations.

Each function relies on the same internal logic: the as-of date is located within the appropriate FII time entity, the entity's sequence is retrieved, and the package then walks backward the number of sequences dictated by the comparison type ('Y' for year-over-year or the alternate prior-year basis). A local variable named l_date holds the computed result before it is returned. The comparison logic is expressed as set-based SELECT statements over sequences with a rownum filter, which is the technique referenced when users search for l_date in this package.

Tables Accessed

The package reads, but does not write, the following tables through APPS synonyms:

• FII_TIME_ENT_YEAR — Supplies the year sequence and start/end dates used for YTD calculations.
• FII_TIME_ENT_QTR — Supplies the quarter sequence, year identifier, and start date used for QTD calculations.
• FII_TIME_ENT_PERIOD — Supplies the period sequence and start/end dates used for MTD calculations.
• FII_TIME_WEEK — Provides the week-level calendar used where weekly boundaries are required.

All four are read-only dimensional sources; the package adds no DML and creates no temporary objects.

Usage Notes

POA_DBI_CALENDAR_PKG is referenced by four other packages, all of which consume its date functions to build dashboard queries and concurrent program outputs. It is normally invoked indirectly — from a DBI report, a menu-driven dashboard form, or a custom concurrent program — rather than called directly by end users. Because it depends entirely on the FII time dimension, the enterprise calendar must be populated through the standard calendar setup before the functions return meaningful results; an incomplete or missing calendar will produce a NO_DATA_FOUND condition on the sequence lookup. Typical invocations pass 'YTD', 'QTD', or 'MTD' as period type and 'Y' or an alternate value as comparison type. The header timestamp (115.3, dated 2002) indicates the package has been stable across releases and remains compatible with both 12.1.1 and 12.2.2.