Search Results delete_dm_binvalues_for_field

Overview

AMS_DM_BINVALUES_PVT is a private (PVT) PL/SQL package in the APPS schema that supports the Oracle Marketing (AMS) data-mining subsystem, specifically the maintenance of bin values used to define value ranges or buckets for source fields in data-mining models. The package declares AUTHID CURRENT_USER and is identified in its header as version 115.3, dated 2002/12/09, sourced from amsvdbvs.pls. Its declared purpose section is intentionally empty, reflecting that it is an internally generated, private API whose public interface is exposed through a companion package.

The central data structure is the record type dm_binvalues_rec_type, which maps attribute-for-attribute to the columns of the AMS_DM_BIN_VALUES table: bin_value_id, last_update_date, last_updated_by, creation_date, created_by, last_update_login, object_version_number, source_field_id, bucket, bin_value, start_value, and end_value. Each attribute is initialized to the corresponding FND_API.G_MISS_* sentinel, enabling the standard Oracle EBS API convention in which unmodified columns are distinguished from intentionally nulled values. The package also defines the constant G_DEFAULT_NUM_REC_FETCH (30), which controls how many records are fetched per bulk call.

Key Procedures and Functions

The metadata documents eight procedures/functions, corresponding to the standard insert-update-delete-validate pattern for a private entity API:

• CREATE_DM_BINVALUES — creates a new bin value row in AMS_DM_BIN_VALUES, populating both the base table and, via the _S synonym, the audit/shadow table.
• UPDATE_DM_BINVALUES — modifies an existing bin value record, honoring the G_MISS sentinels so that unchanged attributes are preserved.
• DELETE_DM_BINVALUES — removes a single bin value row.
• LOCK_DM_BINVALUES — the routine most directly relevant to the searched term "lock_dm_binvalues": it places a row-level lock on a bin value row (typically via SELECT FOR UPDATE semantics) before a subsequent update or delete, preventing concurrent modification of the same record.
• VALIDATE_DM_BINVALUES — performs entity-level validation of the supplied record, checking required fields and business rules.
• CHECK_DM_BINVALUES_ITEMS — verifies that dependent/child items associated with a bin value exist or are consistent, supporting referential integrity.
• VALIDATE_DM_BINVALUES_REC — record-level validation, applying the record type to ensure all mandatory attributes and data types are correctly formed.
• DELETE_DM_BINVALUES_FOR_FIELD — bulk-deletes bin values associated with a given source field (source_field_id), used when a source field is removed or its bucketing definitions are reset.

Tables Accessed

The package references the following objects via APPS synonyms:

• AMS_DM_BIN_VALUES — the primary transactional table storing bin_value_id, source_field_id, bucket, bin_value, start_value, and end_value, along with the standard WHO columns and object_version_number.
• AMS_DM_BIN_VALUES_S — the shadow/audit table maintained in parallel with the base table, capturing the "as-of" change history used by concurrent programs and reporting.
• DUAL — used for single-row selects, typically in validation checks or sequence retrieval.

Together these tables represent the persistence layer for the value-bucketing definitions that drive Oracle Marketing's data-mining and segmentation features.

Usage Notes

Because this is a private package, it is not intended for direct invocation by external custom code. It is called internally by its public-API counterpart and by any of the two packages documented as referencing it in ETRM. Typical callers include Oracle Marketing data-mining forms and concurrent programs that maintain source-field binning definitions.

When customizing or troubleshooting, note that the LOCK_DM_BINVALUES routine should be invoked before UPDATE_DM_BINVALUES or DELETE_DM_BINVALUES to guarantee serialized access to the row and to avoid the ORA-01410/ORA-08177 class of concurrent-access errors. All G_MISS sentinel values must be passed for attributes that should remain untouched. The G_DEFAULT_NUM_REC_FETCH constant (30) governs bulk fetch sizing in any loop-based traversal. The AUTHORIZATION clause AUTHID CURRENT_USER means the package executes with the privileges of the calling user, which is consistent with other private APIs shipped in the AMS schema.