Search Results end_position




Overview
Explore in AI Chat

Overview

GMD_CREATE_FORMULA is a PL/SQL package body owned by the APPS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It belongs to the Oracle Process Manufacturing (OPM) product family, as indicated by the GMD prefix and its inclusion in the ETRM documentation set. The package's stated purpose, per its embedded header comment, is to "call the formula API." It functions as a data-conversion and load utility that parses a delimited flat file containing formula definitions and submits that data to the public formula API, GMD_FORMULA_PUB, through its formula_insert_hdr_tbl_type table type. In practical terms, the package exists to migrate or bulk-load formula master data into OPM from external CSV extracts, bypassing manual keying through the Forms interface. The ETRM metadata classifies the package as API classification OTHER and confirms a single documented entry point, making it a narrow, purpose-built loader rather than a general-purpose business API.

Key Procedures and Functions

  • CREATE_FORMULA — The sole documented procedure in the package body. It reads an external text file line by line using UTL_FILE.GET_LINE, parses each line into comma-separated fields, and populates elements of a formula_insert_hdr_tbl_type collection. Parsing is performed with INSTR and SUBSTR driven by the start_position and end_position variables, which advance through the record one delimiter at a time. Extracted values include the record type, formula number (upper-cased), and subsequent header attributes. Once populated, the collection is passed to the formula API for insertion. The procedure also tracks timing using DBMS_UTILITY.GET_TIME and maintains a counter of processed records.

No other procedures or functions are documented for this package. The ETRM record notes that GMD_CREATE_FORMULA is not referenced by any other package, confirming that CREATE_FORMULA is a terminal, independently invoked routine.

Tables Accessed

The ETRM metadata lists two dependencies accessed through APPS synonyms: DBMS_UTILITY and UTL_FILE. Neither is a business data table; both are Oracle-supplied PL/SQL packages. UTL_FILE provides server-side file I/O, and CREATE_FORMULA uses it to open and read the source CSV file. DBMS_UTILITY supplies timing and utility services, specifically GET_TIME for elapsed-time measurement. The target formula data is not written directly by this package. Instead, records are staged into the formula_insert_hdr_tbl_type collection and submitted to the GMD_FORMULA_PUB public API, which performs the actual insert into the OPM formula tables on the package's behalf. This API-mediated design keeps the loader insulated from changes to the underlying formula schema.

Usage Notes

CREATE_FORMULA is a server-side conversion utility, not a menu-driven concurrent program or a Forms-triggered routine. It is typically invoked by a DBA or technical consultant from SQL*Plus or a similar tool, or wrapped inside a custom concurrent program during an OPM implementation or data-migration project. Before execution, the source file path referenced in the package body must be configured for the target environment; the shipped value points to a development directory and carries an explicit comment that it requires change. The file must be readable by the database server and present on the database host, since UTL_FILE operates server-side. Output is observable only through the API return status and message count variables, and the package emits no report or log file. Because the routine loads formulas directly through the public API, standard API validation rules and error messages apply, and any rejected records must be identified through the API message stack rather than from a package-generated audit trail. The package should be exercised in a test instance before production use, given its hard-coded file location and lack of restart or error-recovery logic.