Search Results get_as_of_date

Overview

PNP_UTIL_FUNC is a shared utility package owned by the APPS schema in Oracle E-Business Suite, classified under the Property Manager (PN) product family. It provides a centralized library of PL/SQL helper routines, record type definitions, and global variables that support real estate, lease, and space management processing across the Property Manager application. The package is declared with AUTHID CURRENT_USER, meaning that all SQL statements it executes are parsed and executed under the privileges of the calling user rather than the package owner, which is significant when the package is invoked from forms, concurrent programs, or custom code running under a restricted schema.

The user query referenced g_mini_retro_enabled, a package-level global BOOLEAN declared with a default value of TRUE. This variable belongs to a group of global state variables defined at the top of the package specification, alongside g_retro_enabled (default FALSE), g_as_of_date (initialized to SYSDATE), and the boundary constants g_start_of_time and g_end_of_time. These globals govern date-effective (retroactive) query behavior for public views such as the location and employee public views, which is why the package exposes dedicated view-context globals and accessor logic. In practice, g_mini_retro_enabled acts as a lightweight switch controlling reduced-scope retroactive date logic, distinct from the full retro processing controlled by g_retro_enabled.

Key Procedures and Functions

The documented package metadata lists 122 procedures and functions. Representative members include:

• VALIDATE_LEASE_TERMINATE_DATE / MIN_LEASE_TERMINATE_DATE — validate and derive the earliest permissible lease termination date based on lease terms and payment schedules.
• ITEM_END_DATE / FETCH_ITEM_END_DATES — determine end dates for payment items and retrieve them in bulk.
• NORM_TRM_EXSTS — checks whether a normal (non-retroactive) payment term exists for a given lease context.
• GET_TOTAL_PAYMENT_ITEM_AMT / GET_TOTAL_PAYMENT_TERM_AMT — aggregate monetary amounts at the payment item and payment term levels.
• GET_CONCATENATED_ADDRESS — builds a formatted address string from location components.
• GET_VACANT_AREA / GET_VACANT_AREA_PERCENT / GET_VACANCY — calculate vacant space metrics.
• GET_OCCUPANCY_PERCENT / GET_UTILIZED_CAPACITY / GET_AREA_UTILIZED / GET_TOTAL_LEASED_AREA — compute occupancy and utilization statistics used in space reporting.
• GET_LOAD_FACTOR / GET_FLOORS / GET_OFFICES — return building geometry and load-factor attributes for space planning.
• GET_LEASE_STATUS — resolves the current status of a lease.
• PN_DISTINCT_ZIP_CODE — returns distinct postal codes for filtering and validation.

Tables Accessed

Through APPS synonyms, the package reads a broad set of HR, project, and property tables. HR tables (PER_ALL_PEOPLE_F, PER_ALL_ASSIGNMENTS_F, PER_JOBS, PER_PERSON_TYPES_TL, PER_PHONES) support the emp_hr_data_rec record type used to resolve employee details. Project tables (PA_PROJECTS, PA_TASKS) back the emp_pr_data_rec and emp_tr_data_rec types for project and task context. Property tables (PN_LOCATIONS, PN_LOCATIONS_ALL, PN_LOCATION_PARKS, PN_PROPERTIES, PN_SPACE_ASSIGN_CUST_ALL, PN_SPACE_ASSIGN_EMP_ALL, PN_PAYMENT_TERMS_ALL, PN_CURRENCIES) supply lease, space assignment, payment term, and location data. These reads underpin the area, occupancy, and lease computations.

Usage Notes

PNP_UTIL_FUNC is referenced by 51 other packages, indicating it is a foundational dependency rather than an end-user entry point. It is typically invoked from Property Manager forms, concurrent programs performing lease and space reporting, and custom extensions that require consistent date-effective or occupancy calculations. Because of its global variables, callers should be aware that toggling g_mini_retro_enabled or g_retro_enabled affects subsequent public view queries within the same session; these values should be reset after use to avoid leaking state between operations.