Search Results generate_query




Overview
Explore in AI Chat

Overview

ASG_CUSTOM_PVT is a private PL/SQL package in the APPS schema that belongs to the Oracle Applications Framework product family associated with the ASG (Application Object Library / CRM Foundation) module. Its principal business function is the runtime customization of publication items used by the Oracle download and publication infrastructure. Publication items define which base tables or views are exposed for extraction, which columns constitute the primary key, which additional columns are published, and what optional filter predicates constrain the result set. ASG_CUSTOM_PVT allows administrators and calling code to redefine these publication items dynamically without altering the seeded definitions delivered by Oracle, thereby supporting customer-specific data set configurations, selective refresh propagation, and on-demand query generation.

The package is declared with AUTHID CURRENT_USER, meaning its unqualified database object references resolve against the privileges of the invoking session rather than the definer. The package-level constants G_PKG_NAME and G_FILE_NAME identify the package and source file for the messaging and error-logging routines used when unexpected conditions arise.

Key Procedures and Functions

The documented API surface comprises twelve procedures and functions, of which the following are central:

  • CUSTOMIZE_PUB_ITEM — Redefines a custom publication item by supplying its name, a base table or view, a comma-separated list of primary key columns, a comma-separated list of non-key data columns, and an additional filter predicate to be appended to the publication item's existing WHERE clause. It returns a status indicating success or failure together with a descriptive error message.
  • MARK_DIRTY — Marks publication items or associated records as dirty so that the download/publication engine will reprocess them. Overloads accept an access list, user identifier list, DML list, and a timestamp to scope the affected rows.
  • EXEC_CMD — Executes a command string, typically used internally to run dynamic SQL constructed during customization processing. This is the routine most closely associated with the "exec_cmd" search term and should be treated as a private helper rather than a publicly supported entry point.
  • GENERATE_WHERE — Builds the WHERE clause fragment for a publication item from the primary key columns, data columns, and additional filter predicate supplied during customization.
  • GENERATE_QUERY — Assembles the full extraction query for a publication item, using the components produced by GENERATE_WHERE.
  • FIND_NUM_PKCOLS — Determines the number of primary key columns defined for a given publication item, used to validate the column specification supplied by the caller.
  • GET_COL — Retrieves column-level metadata, used in conjunction with FIND_NUM_PKCOLS to validate that the named columns exist on the base table or view.
  • LOG — Writes diagnostic or error information to the standard logging mechanism for later review.

Tables Accessed

The package reads and updates the following objects through APPS synonyms:

  • ASG_PUB — The publication definition header, holding the identity and attributes of each publication.
  • ASG_PUB_ITEM — The publication item definition, including the base table, key columns, data columns, and filter predicate that CUSTOMIZE_PUB_ITEM modifies.
  • ALL_SYNONYMS — Consulted to resolve synonym references when validating the base table or view named in a customization request.
  • DBA_TAB_COLUMNS — Consulted to confirm that columns named by the caller actually exist on the specified base object.

Usage Notes

ASG_CUSTOM_PVT is a private package and is not intended for direct invocation by end users; it is called by the publication and download subsystem and by one other documented package. Typical invocation paths include concurrent programs that refresh or republish download data, and internal processing triggered when publication definitions change. Custom code that requires publication item redefinition should prefer the corresponding public APIs where available, because the parameter contracts of private routines such as EXEC_CMD may change between releases. When customizing in 12.1.1 or 12.2.2, callers should pass a valid API version number, honor the p_init_msg_list flag, and always inspect x_return_status and x_error_message before proceeding. Because the package executes dynamically constructed SQL through EXEC_CMD, customization inputs must be treated as trusted content.