Search Results get_top_opps_sql

Overview

APPS.BIM_DBI_MKTG_MGMT_PVT is a private PL/SQL package that supplies dynamically generated SQL statements to the Oracle EBS Marketing Management dashboard. The package belongs to the Oracle Business Intelligence (formerly Daily Business Intelligence) subsystem and is classified as PVT, indicating that it is an internal implementation object rather than a supported public API. Its responsibilities are confined to Oracle EBS 12.1.1 and 12.2.2, where the package is compiled under the APPS schema and accessed through synonym resolution.

The package does not itself store or persist marketing data. Instead, each procedure accepts a page parameter collection, constructs a query string, and returns that string along with a table of query attribute definitions to the dashboard rendering engine. This design allows the Marketing Management dashboard to present KPIs, charts, tables, and ranking reports without embedding complex SQL in the presentation layer.

The header comment (bimvsqls.pls, version 120.1, dated 2005/09/19) confirms the package originated in the DBI marketing SQL framework and has remained substantially unchanged through the 12.x release family.

Key Procedures and Functions

The documented interface consists of approximately 29 procedures, all following the same contract: they receive a page parameter table and return an OUT NOCOPY custom SQL string and an OUT NOCOPY attribute table. The procedures are grouped by marketing reporting function.

• GET_KPI_SQL — Builds the SQL for the main marketing KPI region.
• GET_CPL_KPI — Returns the SQL for the cost-per-lead KPI.
• GET_PO_RACK_SQL and GET_CS_RACK_SQL — Generate SQL for partner and customer pipeline/ranking rack displays.
• GET_CPL_GRAPH_SQL — Produces the cost-per-lead trend chart query.
• GET_TOP_LEADS_SQL, GET_TOP_OPPS_SQL — Return base queries for top lead and top opportunity listings.
• GET_TOP_EVEH_OPPS_SQL, GET_TOP_EVEH_LEAD_SQL — Provide top opportunity and lead queries scoped to events.
• GET_TOP_CAMP_OPPS_SQL, GET_TOP_CAMP_LEAD_SQL — Provide top opportunity and lead queries scoped to campaigns. GET_TOP_CAMP_LEAD_SQL is the procedure most directly associated with the user search term "get_top_camp_lead_sql," which refers to the top campaign lead ranking report.
• GET_CPL_RPL_SQL, GET_RPL_CPL_SQL — Generate cost-per-lead and response-per-lead conversion queries.
• GET_LEAD_OPTY_CONV_SQL — Returns the lead-to-opportunity conversion query.
• GET_MKTG_A_LEADS_SQL, GET_MKTG_NEW_LEADS_SQL — Build queries for active and newly created marketing leads.
• GET_CAMP_START_SQL, GET_EVEH_START_SQL, GET_CSCH_START_SQL — Generate queries for campaigns, events, and campaigns starting within a period.
• GET_RESP_SUM_SQL — Returns the marketing response summary query.

Tables Accessed

The package references a limited set of tables through APPS synonyms:

• BIM_I_ADMIN_GROUP — Supplies administrative group definitions used to filter marketing data by organizational hierarchy.
• BIM_I_SOURCE_CODES — Provides source code lookups that classify lead and opportunity origins for ranking and conversion queries.
• JTF_RS_RESOURCE_EXTNS — The resource extension table used to resolve sales and marketing resource attributes, including ownership and territory information.
• PLITBLM — A PL/SQL index-by table type used internally to hold intermediate result sets during SQL construction.

These references are primarily read-only lookups. No transaction tables are modified, consistent with the package's role as a query generator.

Usage Notes

BIM_DBI_MKTG_MGMT_PVT is invoked exclusively by the Marketing Management dashboard framework, which calls the appropriate procedure for each dashboard region and then executes the returned SQL. It is not intended for direct invocation by customers, forms, or concurrent programs, and it is not referenced by any other documented package (0 dependent packages).

Because the object is a PVT package, Oracle does not guarantee its signature across releases; patches may alter the procedure set without notice. Customizations that require marketing ranking logic, such as top campaign leads, should therefore be implemented through supported DBI extension mechanisms rather than by calling this package directly. In 12.1.1 and 12.2.2 the package remains part of the standard APPS schema installation.