Search Results g_package_name

Overview

The APPS.JTM_CON_QUERY_REQUEST_PKG package body is a component of the Oracle E-Business Suite Trading Community / Customer Data Management infrastructure (the JTM schema prefix). Its primary business function is to drive the execution of stored "query requests" that populate publication data used by the resource subscription model within EBS. The package iterates over configured query requests, executes the associated SQL against source tables, and makes the results available to downstream publication items that resources (users) may subscribe to. It also contains a remediation utility (FIX_DFF_ACC) that repairs descriptive flexfield access records, and a thin wrapper procedure (WorkAround) that invokes it.

The package is marked with the header identifier $Header: jtmconqb.pls 120.3 2006/01/13 ... noship $, indicating it is a nonshipped internal component. It is referenced by one other package, confirming it forms part of a coordinated processing chain rather than a standalone entry point.

Key Procedures and Functions

• RUN_QUERY_REQUESTS — The principal driver procedure. It consumes the c_query_requests cursor, which selects con_query_id, acc_table_name, con_query, and last_run_date from jtm_con_query_request_data where EXECUTE_FLAG='Y', ordered by execution_order. This enforces a deterministic, dependency-aware processing sequence. The procedure executes each configured query and updates run metadata (such as last_run_date).
• WORKAROUND — A thin wrapper that calls FIX_DFF_ACC, passing local status and message variables. It serves as an externally invocable entry point for the flexfield access repair logic, isolating callers from the detailed signature of FIX_DFF_ACC.
• FIX_DFF_ACC — A repair procedure taking status and message OUT parameters. It uses two cursors: get_dff_seed_date (reading creation_date, application_id, base_application_id, and descriptive_flexfield_name from JTM_FND_DESCR_FLEXS_ACC) and get_old_context_acc (reading access_id from JTM_FND_DESC_FLEX_CONTEXT_ACC filtered by creation date and application/DFF identity). The procedure reconciles legacy or mismatched descriptive flexfield access records.

Note that the package declares global variables G_PACKAGE_NAME, g_debug_level, and g_category. A fourth documented routine exists in the metadata count (4 total) beyond the three named; the excerpt above confirms the three listed.

Tables Accessed

• JTM_CON_QUERY_REQUEST_DATA — Drives the driver cursor; supplies query text, target table, run date, and execution flags.
• ASG_PUB_ITEM and JTM_PUB_ACC — Joined by the c_primary_key cursor to resolve the primary key column of the publication item associated with a query.
• ASG_USER, ASG_USER_PUB_RESPS, and ASG_PUB_ITEM — Joined by c_item_resources to enumerate resources subscribed to a publication item (added via Bug 4924543).
• JTM_FND_DESCR_FLEXS_ACC, JTM_FND_DESC_FLEX_CONTEXT_ACC, JTM_FND_DESC_FLEX_COL_USG_ACC, JTM_FND_FLEX_VALUES_ACC, and JTM_FND_FLEX_VALUE_SETS_ACC — Flexfield access and usage shadow tables used by the repair routine.
• FND_DESCR_FLEX_COLUMN_USAGES and FND_FLEX_VALUES — Standard FND flexfield definition and value tables (accessed via APPS synonyms) supporting flexfield reconciliation.
• JTM_CON_REQUEST_DATA — Additional request configuration data referenced in processing.
• DBMS_SQL and PLITBLM — Dynamic SQL execution and PL/SQL index-by table utilities used to run the stored query text and buffer results.

Usage Notes

In Oracle EBS 12.1.1 and 12.2.2, JTM_CON_QUERY_REQUEST_PKG is an internal processing package, not a user-facing API. RUN_QUERY_REQUESTS is typically invoked by a concurrent program or a scheduling job that periodically refreshes publication item data; administrators control which queries execute by setting EXECUTE_FLAG='Y' and ordering via execution_order in JTM_CON_QUERY_REQUEST_DATA. The WORKAROUND/FIX_DFF_ACC pair is an ad hoc remediation utility, historically invoked by support or patching scripts to correct descriptive flexfield access records rather than through routine UI. Because it depends on dynamic SQL and internal JTM shadow tables, direct custom invocation is discouraged; changes should be made through the governed query-request configuration rows. The "noship" header status indicates the package is private and may change without notice across patch levels.