Search Results derive_bill_sequence_id

Overview

MSC_ST_UTIL is a shared utility package in the APPS schema that supports the Oracle Advanced Supply Chain Planning (ASCP) staging and data-loading infrastructure within Oracle E-Business Suite. It provides the common services required when external or legacy planning data is loaded into the MSC staging tables: deriving surrogate internal identifiers from user-facing names or codes, validating descriptive attributes, standardizing error capture, and managing instance and process context. The package is declared with AUTHID CURRENT_USER, meaning its SQL statements execute with the privileges of the calling schema, and its header carries a version stamp dated 2012, consistent with the 12.1.1 and 12.2.2 release lines. The presence of a large set of constants (status flags such as G_NEW, G_IN_PROCESS, G_ERROR, G_VALID, severity levels, and sentinel values for NULL_DATE, NULL_VALUE, and NULL_CHAR) establishes it as a central registry of literal values used throughout the ASCP staging code base. The user search term "derive_proj_task_id" refers to one of its documented derivation routines, DERIVE_PROJ_TASK_ID, which resolves a project task identifier during inbound data processing.

Key Procedures and Functions

The documented interface contains 43 procedures and functions. The derivation family is the most visible: DERIVE_COMPANY_ID, DERIVE_ITEM_ID, DERIVE_PARTNER_ORG_ID, DERIVE_PARTNER_SITE_ID, and DERIVE_PROJ_TASK_ID each convert a natural business key supplied in a staging record into the internal numeric identifier required by downstream planning tables, returning a default value and error text when resolution fails. DERIVE_BILL_SEQUENCE_ID, DERIVE_ROUTING_SEQUENCE_ID, DERIVE_LEVEL_ID, DERIVE_SR_LEVEL_PK, DERIVE_SR_PK, and DERIVE_LEVEL_PK perform the equivalent resolution for bill of material sequences, routing sequences, and sourcing rule level primary keys.

Diagnostic and control routines include LOG_ERROR, which records a staging row failure with a severity and returns a status, LOG_MESSAGE for informational output, and GET_ERROR_MESSAGE for retrieving formatted error text. VALIDATE_UOM and VALIDATE_DMD_CLASS verify unit of measure and demand class values before a record is accepted. GET_INSTANCE_CODE returns the current ASCP instance context. SET_PROCESS_FLAG and RETN_DELETE_FLAG and RETN_SCHEDULE_ID support process state management and deletion or scheduling decisions during the load cycle.

Tables Accessed

The package reads and writes a compact set of reference and control tables, all accessed through APPS synonyms. FND_RESPONSIBILITY supplies responsibility context. MSC_APPS_INSTANCES holds the registered planning instances that determine the instance code used throughout derivation and error logging. MSC_COMPANIES and MSC_COMPANY_USERS provide the company and user mappings used by DERIVE_COMPANY_ID and its related lookups. MSC_ERRORS is the destination for LOG_ERROR output. MSD_ST_TIME and MSD_TIME provide time and scheduling reference values used in sequence and schedule derivation. DUAL is used for constant evaluation and single-row expressions.

Usage Notes

MSC_ST_UTIL is not an end-user driven package; it is invoked programmatically by ASCP staging interfaces, collection and loading concurrent programs, and the fourteen packages documented as its callers. Custom integrations that populate the MSC staging tables, such as external planning extracts or interface tables loaded by SQL*Loader or PL/SQL, should call these derivation functions rather than resolving identifiers independently, because the routines encapsulate the same validation and error-logging behavior used by Oracle's own loaders. Error rows should always be written through LOG_ERROR so that failures appear consistently in the ASCP interface error reporting views. The constants defined in the header should be referenced symbolically (for example, G_SEV_ERROR or NULL_VALUE) to avoid hard-coded literals and to remain compatible across 12.1.1 and 12.2.2 patches.