Search Results is_time_between

Overview

HZ_COMMON_PUB is a public utility package in the Oracle E-Business Suite Trading Community Architecture (TCA) and Receivables schema, owned by APPS and declared with AUTHID CURRENT_USER. It provides a shared library of low-level helper routines that other TCA-related APIs, forms, and concurrent programs call to perform common tasks: controlling transaction boundaries, validating lookup values, enforcing mandatory column rules, retrieving descriptive names and addresses for parties, customers, and contacts, generating surrogate key values for account and party site entities, and managing content source type security. Because the package consolidates these frequently repeated operations, it reduces redundant SQL across the many modules that populate HZ_CUST_ACCOUNTS, HZ_PARTIES, and related entities.

The package is classified as PUB, indicating that its interface is considered stable for external invocation. It is referenced by approximately 30 other packages, confirming its role as foundational infrastructure rather than an end-user API. The source header identifies it as ARHCOMMS.pls, a legacy HZ (Trading Community) common utility module.

Key Procedures and Functions

The documented interface contains 29 procedures and functions, grouped by purpose:

Transaction control: COMMIT_TRANSACTION and ROLLBACK_TRANSACTION provide standardized commit and rollback handling, typically invoked by calling APIs to finalize or reverse work.
Environment check: IS_TCA_installed returns a Boolean indicating whether Trading Community Architecture is enabled, allowing callers to branch behavior.
Lookup validation: VALIDATE_LOOKUP and VALIDATE_FND_LOOKUP verify that a given column value exists as a valid lookup code of a specified lookup type, returning a status indicator.
Numbering helpers: GET_ACCOUNT_NUMBER and GET_PARTY_SITE_NUMBER retrieve sequential surrogate identifiers for customer accounts and party sites.
Content source security: CONTENT_SOURCE_TYPE_SECURITY, ENABLE_CONT_SOURCE_SECURITY, and DISABLE_CONT_SOURCE_SECURITY control access filtering on content sources.
Name and address retrieval: GET_CUST_ADDRESS, GET_CUST_NAME, GET_CUST_CONTACT_NAME, and GET_PARTY_NAME return descriptive text for a given identifier. These functions suit narrow queries such as the user’s search for get_cust_name. Each carries a PRAGMA RESTRICT_REFERENCES (WNDS, WNPS, RNPS), certifying that it writes no database state or package state and reads no package state — making them safe to call from SQL statements.
Mandatory column checks: CHECK_MANDATORY_STR_COL, CHECK_MANDATORY_DATE_COL, and CHECK_MANDATORY_NUM_COL validate whether a required string, date, or numeric column is populated, respecting create-versus-update context flags and allowable-missing settings.
Time comparisons: TIME_COMPARE, IS_TIME_BETWEEN, and IS_TIME_OVERLAP support temporal validation logic.

Tables Accessed

The package reads and writes a defined set of HZ tables through APPS synonyms:

HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES_ALL — customer account, role, and site data used by the get_cust_* functions.
HZ_PARTIES, HZ_PARTY_SITES, HZ_RELATIONSHIPS — party name resolution and relationship data for GET_PARTY_NAME and related calls.
HZ_ACCOUNT_NUM_S and HZ_PARTY_SITE_NUMBER_S — sequences tables supplying account and party site numbers.
HZ_LOCATIONS — address data for GET_CUST_ADDRESS.
FND_LOOKUP_VALUES — source of valid lookup codes for the validate routines.
DBMS_SESSION and DUAL — session-level settings and singleton queries.

Usage Notes

Because HZ_COMMON_PUB is a PUB-classified shared utility, it is invoked both directly from Oracle Forms and PL/SQL APIs within the TCA and Receivables modules, and indirectly through the roughly 30 packages that reference it. Custom code should treat the documented routines as stable, though callers must remember that the four name/address functions are pure read-only queries constrained by RESTRICT_REFERENCES, while COMMIT_TRANSACTION and ROLLBACK_TRANSACTION alter transaction state and must be used deliberately. Standard practice is to let the higher-level TCA or AR APIs manage commits where possible, reserving the commit and rollback helpers for scenarios where the calling program owns the transaction boundary. The package is present across EBS 12.1.1 and 12.2.2 releases without interface changes in the documented metadata.