Search Results check_exists

Overview

CS_SERVICE_AVAIL_PKG is a service-related PL/SQL package in the Oracle E-Business Suite (EBS) Service (CS) module. Its documented body contains a single public procedure, DUPLICATE_CHECK, which encapsulates the duplicate-detection logic used when maintaining service availability records. The package belongs to the APPS schema (APPS.CS_SERVICE_AVAIL_PKG) and is classified as an OTHER API in the ETRM 12.2.2 metadata. In EBS 12.1.1 and 12.2.2, the package supports the Service Availability feature, which allows organizations to define, for a given service inventory item, the specific available inventory items that may be used in a service context — subject to manufacturing organization, customer, revision range, effective dates, and a service-available flag. Because users can create multiple availability combinations, the database includes a uniqueness constraint enforced, in part, at the application layer. DUPLICATE_CHECK exists to pre-validate a proposed combination so that the calling form or program can reject or warn about duplicates before an INSERT or UPDATE against the underlying table is attempted.

Key Procedures and Functions

• DUPLICATE_CHECK — Determines whether a service availability record matching a supplied set of business attributes already exists in the repository. It accepts an event indicator, the service inventory item identifier, the available inventory item identifier, the manufacturing organization identifier, the customer identifier, a low and high revision number, an availability start and end date, low and high date boundary defaults, the service-available flag, and the service availability identifier of the record currently being edited. It returns an OUT flag indicating existence (documented behavior: 'Y' when a matching row is found, 'N' otherwise). The procedure compares the candidate attribute set against existing rows, treating null attributes as equal via NVL normalization to -1, comparing dates on their truncated (day) components, and excluding the current record's primary key so that a record is not reported as a duplicate of itself. The documented implementation uses an internal cursor that selects a constant from CS_SERVICE_AVAILABILITY, opens it, fetches a single row, and sets the OUT flag accordingly.

Tables Accessed

• CS_SERVICE_AVAILABILITY — Accessed through the APPS synonym. The package performs a read-only existence query against this table; it does not insert, update, or delete rows. The columns matched by DUPLICATE_CHECK are service_inventory_item_id, inventory_item_id, item_manufacturing_org_id, customer_id, revision_low, revision_high, start_date_active, end_date_active, service_available_flag, and service_availability_id. The table is the master repository for service availability definitions, and this package's query effectively mirrors the logical unique key of that entity.

Usage Notes

DUPLICATE_CHECK is typically invoked from the Service Availability maintenance UI — the Oracle Service forms that create and update service availability records — and may also be called from custom client extensions or concurrent/custom code that loads service availability data. Callers supply the candidate attribute values plus the primary key of the record being edited (p_service_avail_id), then branch on the returned p_exists_flag: 'Y' signals that an equivalent row exists and the save should be blocked or confirmed, while 'N' permits the write. Key behavioral points to retain when reusing or reimplementing this logic: null semantics are normalized using NVL(..., -1) on the numeric and date boundary fields, dates are compared at day granularity, and the record's own identifier is excluded from the match. The package is referenced by zero other packages in the documented metadata, so it is a leaf-level utility rather than a shared framework API. As with any APPS schema object, direct modification is not supported; customizations should be layered through supported extension mechanisms.