Search Results nls_date_format

Overview

FND_CONC_DATE (AFCPDATB.pls) is a low-level Oracle E-Business Suite utility package owned by APPS that provides centralized date string interpretation and date format resolution for concurrent processing and profile-driven logic. Its primary business function is to safely convert arbitrary date strings supplied through profile options and concurrent program parameters into Oracle DATE values, without prior knowledge of the format used by the caller or end user. In multilingual and multi-region EBS 12.1.1 and 12.2.2 deployments, dates reach the application from many sources: FND profile values, concurrent program parameter entry fields, and third-party interfaces. Each source may express a date differently depending on the session NLS environment. FND_CONC_DATE eliminates the ambiguity by attempting a deterministic series of format masks, returning the first successful conversion. The package is therefore a foundational dependency for request submission, parameter validation, and any custom code that must parse dates originating outside a strongly typed interface.

Key Procedures and Functions

• STRING_TO_DATE — Converts a VARCHAR2 date string into a DATE. The function attempts conversion using the following format masks in order: (1) the session NLS_DATE_FORMAT; (2) 'DD-MON-RR' or 'DD-MON-YYYY'; (3) 'DD-MM-RR' or 'DD-MM-YYYY'; and (4) the AOL canonical format 'YYYY/MM/DD'. It also tolerates an optional time component in the form HH24:MI or HH24:MI:SS appended to each candidate mask. Length comparisons against a formatted SYSDATE are used to select the appropriate mask for the string, and an intentional exception is raised via TO_DATE(string, 'ABC') to force fall-through to the next mask group when a match fails. If all attempts fail, the exception propagates to the caller.
• GET_DATE_FORMAT — Returns the date format applicable to the current session or context, providing a companion utility for callers that need to know the active format mask before constructing or displaying dates. This function underpins the format-resolution behavior used by STRING_TO_DATE.

Tables Accessed

The package queries NLS_SESSION_PARAMETERS (accessed via the APPS synonym) to retrieve the current value of the NLS_DATE_FORMAT parameter. This lookup is essential because the first conversion attempt depends entirely on the session's active NLS date format, which varies by user preference and language territory. No tables are modified; FND_CONC_DATE is read-only with respect to the database, and all dynamic date resolution is performed against SYS.DUAL for length calculations and DECODE-based mask selection. The exclusive dependence on NLS_SESSION_PARAMETERS keeps the package lightweight and free of side effects, making it safe to call repeatedly within concurrent request processing.

Usage Notes

FND_CONC_DATE is typically invoked indirectly. The concurrent manager and concurrent program parameter framework call STRING_TO_DATE when a date parameter is submitted from the Standard Request Submission form or the concurrent programs interface, converting the entered string into a DATE for the request. Profile option values containing dates are likewise parsed through this function. The package is referenced by 15 other packages across the APPS schema, confirming its role as a shared utility rather than an application-specific API. Custom code should use this package instead of performing ad hoc TO_DATE conversions on user-supplied strings, because it correctly handles the many NLS_DATE_FORMAT permutations present in EBS 12.1.1 and 12.2.2 environments. Developers should note that a fully unparseable string raises an exception rather than returning NULL, so callers must include proper error handling. The presence of the package in both 12.1.1 and 12.2.2 confirms behavioral stability across the release boundary, despite the ONLINE PATCHING and AD/TXK changes introduced in 12.2.