Search Results check_existing_lot_db




Overview
Explore in AI Chat

Overview

INV_LOT_API_PKG is the public PL/SQL API for creating, validating, and deleting lot number records in Oracle Inventory. It encapsulates the business rules that govern lot master data, allowing callers to manage MTL_LOT_NUMBERS rows through a controlled interface rather than through direct DML. The package is owned by APPS and is classified as OTHER within the ETRM inventory of documented APIs.

The package header preserves the original source signature, including the standard return-status constants used throughout Oracle EBS public APIs: g_ret_sts_success ('S'), g_ret_sts_error ('E'), and g_ret_sts_unexp_error ('U'). It also defines the conventional missing-value constants g_miss_num, g_miss_char, and g_miss_date, and a set of source indicators (osfm_form_no_validate, osfm_open_interface, osfm_form_validate, inv) that govern whether validation is suppressed, deferred, or performed. Exception objects g_exc_error and g_exc_unexpected_error are likewise declared for downstream error handling.

Key Procedures and Functions

  • POPULATE_LOT_RECORDS — Populates the parent and child lot records and returns them to the internal Create_Inv_Lot logic. It builds both the output child-lot record and the input lot record, honoring a copy-lot-attribute flag and a source indicator.
  • SET_MSI_DEFAULT_ATTR — Derives lot attribute defaults from MTL_SYSTEM_ITEMS for a given organization and inventory item. It populates grade code, origination type, expiration date, retest date, expiration action code, expiration action date, hold date, and maturity date onto the lot record.
  • VALIDATE_LOT_ATTRIBUTES — Validates the lot attributes supplied by the caller and applies defaulting where attributes are missing, according to existing business logic.
  • VALIDATE_ADDITIONAL_ATTR — Performs validation on the additional, non-core lot attributes beyond those handled by VALIDATE_LOT_ATTRIBUTES. This is the procedure most frequently targeted by callers searching on "validate_additional_attr," and it is the entry point custom code uses to confirm that supplemental attribute values on a lot record conform to expected rules before the record is persisted.
  • DELETE_LOT — Removes a lot record, applying the standard return-status contract.
  • CHECK_EXISTING_LOT_DB — Checks for the existence of a lot in the database, typically used to prevent duplicate lot numbers or to confirm a lot before deletion.

Tables Accessed

The package reads and writes MTL_LOT_NUMBERS as its primary table. It consults MTL_SYSTEM_ITEMS and MTL_SYSTEM_ITEMS_B to derive default lot attributes and item definitions. MTL_PARAMETERS supplies organization-level inventory setup, while MTL_LOT_UOM_CLASS_CONVERSIONS supports unit-of-measure and class conversion checks. Interface and staging tables — MTL_TRANSACTIONS_INTERFACE, MTL_TRANSACTION_LOTS_INTERFACE, MTL_TRANSACTION_LOTS_TEMP, and MTL_MATERIAL_TRANSACTIONS_TEMP — are used when lots are created or validated in the context of transaction processing. MTL_OBJECT_GENEALOGY supports genealogy linkage, and DUAL and PLITBLM are used for utility and diagnostic purposes.

Usage Notes

INV_LOT_API_PKG is invoked from Oracle Inventory and Warehouse Management forms, from transaction-manager flows that create lots through the interface tables, and from custom PL/SQL that needs to validate or delete lot records. Because the metadata records three referencing packages, the API is also called internally by other EBS packages rather than only by external consumers. Callers should supply the standard x_return_status, x_msg_count, and x_msg_data OUT parameters, check the return constant, and use FND_MSG_PUB to retrieve messages when the status is 'E' or 'U'. When validating supplemental attributes, invoke VALIDATE_ADDITIONAL_ATTR after the core record has been assembled, and pass the appropriate source flag so that form-driven validation behaves consistently with the calling context.