Search Results get_cas_no

Overview

GR_DISPATCH_HISTORY_PVT is a private PL/SQL package owned by APPS in the Oracle E-Business Suite, classified under the ETRM scheme as a PVT (private) API. It supports the Oracle Environmental Accounting and Regulatory Compliance (formerly GR) product family, which manages the distribution and tracking of regulatory and safety documents — such as Material Safety Data Sheets (MSDS) and related compliance documentation — to customers, sites, and internal recipients. The package encapsulates the core business logic for recording the dispatch of these documents, retrieving compliance-related reference data, and resolving item and organization attributes required during the dispatch process.

Because it is a private package, GR_DISPATCH_HISTORY_PVT is not intended for direct invocation by external integrations. It is consumed by the supported public APIs within the GR product family, which in turn are exposed to the E-Business Suite forms, concurrent programs, and other Oracle modules. The package declares AUTHID CURRENT_USER, meaning that all SQL statements it executes run with the privileges of the calling schema rather than as the package owner, which places the responsibility for object grants on the calling environment.

Key Procedures and Functions

The documented procedures and functions within the package are:

• CREATE_DISPATCH_HISTORY_REC — The primary procedure of the package. It stores details pertaining to the distribution of regulatory documents. If the document being dispatched has not already been stored in the FND_DOCUMENTS table, the eSignatures file upload API is invoked to create it. The procedure accepts a broad set of parameters covering item identifiers, organization context, recipient information, dispatch metadata (date sent, dispatch method), and document attributes (location, name, version, category, format, description, code, disclosure code, language), and returns a standard return status, message count, and message data.
• GET_CAS_NO — Returns the Chemical Abstracts Service (CAS) number associated with a given item number and organization. This is used to identify the specific regulated substance involved in a dispatch.
• GET_ORGANIZATION_CODE — Returns the organization code for a given organization. This procedure is the object most directly associated with the user's search term, indicating that callers frequently need to resolve an internal organization identifier (such as organization_id) into the human-readable code used in dispatch and reporting logic.
• GET_ITEM_DESC — Returns the description of an inventory item. This supports the composition of dispatch records and documentation where the item description must be printed or transmitted alongside the regulatory document.

Tables Accessed

The package interacts with the following tables through APPS synonyms:

• GR_DISPATCH_HISTORY / GR_DISPATCH_HISTORY_S — The base table and its corresponding sequence or secondary table that hold the historical record of document dispatches created by CREATE_DISPATCH_HISTORY_REC.
• FND_DOCUMENTS, FND_DOCUMENT_CATEGORIES, EDR_FILES_B, GR_UPLOAD_FILE_TMP — Used to check for and persist the underlying regulatory document and its file content when a dispatch is created for a document not yet stored.
• MTL_SYSTEM_ITEMS, MTL_SYSTEM_ITEMS_KFV, MTL_PARAMETERS — The item master and organization parameters tables, used to resolve item descriptions, item attributes, and organization-level defaults.
• FND_USER, FND_LANGUAGES — Provide the user context for the dispatch record and the language of the document or recipient.
• DUAL — Used for singleton lookups and validation queries.

Usage Notes

GR_DISPATCH_HISTORY_PVT is invoked indirectly through the public GR APIs. Typical invocation points include the MSDS and regulatory document dispatch forms in Oracle E-Business Suite, concurrent programs that generate or transmit regulatory documentation, and custom extensions that need to record a dispatch while respecting the same validation and document-upload logic used by the standard product. The ETRM metadata indicates that the package is referenced by two other packages, confirming that it functions as a shared internal service layer rather than an entry point. Developers extending the dispatch process should call the public API rather than this private package, as the private API signature is not guaranteed to remain backward compatible across patches or upgrades.