Search Results get_all_orgs

Overview

The APPS.MSC_CL_FUNCTION package is a utility PL/SQL construct within the Oracle E-Business Suite Advanced Planning (MSC) schema, whose principal responsibility is the management and resolution of organization-level metadata across multiple application instances. In distributed or multi-instance EBS environments, planning and collection processes must reconcile organization identifiers that differ between source and destination instances. MSC_CL_FUNCTION provides the programmatic bridge for that reconciliation, notably through the GET_ALL_ORGS function, which was the object of interest in the originating search.

The package specification header, dated 2005 (MSCCLFNS.pls 120.1), indicates the module has been stable across the 12.1.1 and 12.2.2 release streams. It is classified as "OTHER" under ETRM, meaning it is not a formally published open interface but an internal utility that supports planning data collection and organization mapping.

Key Procedures and Functions

• GET_ALL_ORGS — A function that accepts an organization group identifier and an instance identifier, and returns a VARCHAR2 value. Its role is to resolve or enumerate the set of organizations belonging to a given org group for a specified application instance. This is the routine most commonly queried by developers seeking to understand cross-instance organization resolution.
• UPDATE_DATE_COLUMNS — A procedure that returns an error buffer and return code while accepting an instance identifier and a number of days. It performs maintenance on date-related columns, typically to refresh or shift date values within collection or planning tables over a rolling window.
• CHECK_COL_EXISTS — A procedure (documented in the 12.2.2 metadata although absent from the excerpted specification) that validates the existence of a column, supporting dynamic SQL and schema-version tolerance.

Tables Accessed

• FND_LOOKUP_VALUES — Supplies lookup codes and meanings used to interpret organization group values and other configured reference data.
• MSC_APPS_INSTANCES — The core registry of application instances, used to bind an instance identifier to its connection details and context.
• ALL_TAB_COLUMNS — Queried by CHECK_COL_EXISTS to confirm column presence before dynamic operations.
• DBA_SYNONYMS — Used to resolve APPS synonyms and confirm object visibility across the schema.

Usage Notes

MSC_CL_FUNCTION is typically invoked from Advanced Supply Chain Planning collection logic, concurrent programs, and custom extensions that must resolve organizations across instances. The package spec declares a database link variable (v_dblink) and a table type (TblLstTyp), confirming it is designed for distributed querying. Developers calling GET_ALL_ORGS should pass a valid org group and instance identifier and expect a delimited VARCHAR2 result. Because the package is classified as OTHER, Oracle does not guarantee API stability, and modifications should be avoided. Older releases relied on internal date maintenance via UPDATE_DATE_COLUMNS; the newer 12.2.2 build adds CHECK_COL_EXISTS for defensive schema handling.