6737 lines
236 KiB
JavaScript
6737 lines
236 KiB
JavaScript
#!/usr/bin/env node
|
|
|
|
import fs from "node:fs/promises";
|
|
import path from "node:path";
|
|
import { fileURLToPath } from "node:url";
|
|
import {
|
|
DISPLAY_CHROME_PATH,
|
|
DISPLAY_META_PATH,
|
|
DISPLAY_RUNTIME_HOME,
|
|
DISPLAY_PROFILE_DIR,
|
|
DISPLAY_STATE_PATH,
|
|
ensureParentDir,
|
|
ensureRuntimeTree,
|
|
resolveMetaReadPath,
|
|
resolveRuntimeHome,
|
|
resolveStateReadPath,
|
|
} from "./lib/runtime-paths.mjs";
|
|
import { parseArgs, splitCsv } from "./lib/cli-shared.mjs";
|
|
import { quoteLiteral, toSqlList } from "./lib/sql-literals.mjs";
|
|
import {
|
|
SA_ATTACH_DEFAULT_ALL_STATUSES,
|
|
SA_ATTACH_DEFAULT_CLUSTER,
|
|
SA_ATTACH_DEFAULT_MIN_OPPORTUNITY_PROBABILITY,
|
|
SA_ATTACH_DEFAULT_OPEN_STATUSES,
|
|
SA_ATTACH_DEFAULT_PRODUCT_LOBS,
|
|
SA_ATTACH_DEFAULT_REVENUE_TYPE_GROUPS,
|
|
SA_ATTACH_DEFAULT_SALES_CREDIT_TYPE,
|
|
SA_ATTACH_DEFAULT_SOLUTIONS,
|
|
SA_ATTACH_DEFAULT_WON_STATUSES,
|
|
applyCloseWindowIntentDefaults,
|
|
applyCurrentWorkloadIntentDefaults,
|
|
resolveCommercialClusterFilter,
|
|
resolveCoverageLimit,
|
|
resolveManagerEmailFilter,
|
|
resolveManagerEmailListFilter,
|
|
resolveOptionalClusterFilter,
|
|
resolveOwnerEmailFilter,
|
|
resolveResourceFilter,
|
|
resolveSaAttachFilters,
|
|
} from "./lib/sa-attach-filters.mjs";
|
|
import {
|
|
cookieHeaderFor,
|
|
extractClientBin,
|
|
extractCsrfToken,
|
|
inferRuntimeHome,
|
|
isAuthRefreshRequiredError,
|
|
mergeCookiesFromHeader,
|
|
normalizeCookie,
|
|
persistSessionArtifacts,
|
|
readJson,
|
|
replaceObjectContents,
|
|
} from "./lib/auth-shared.mjs";
|
|
import {
|
|
hasAnyDateWindowArgs,
|
|
resolveDateWindow,
|
|
resolveQuarterInput,
|
|
} from "./lib/date-windows.mjs";
|
|
import {
|
|
buildApiMetrics,
|
|
buildWarnings,
|
|
emitJson,
|
|
mapRows,
|
|
} from "./lib/output-contract.mjs";
|
|
import {
|
|
buildSrCoverageKey,
|
|
hasUsableOpportunityId,
|
|
summarizeCurrentTeamOpportunityCoverage,
|
|
summarizeOrgWideRevenueLineCoverage,
|
|
} from "./lib/coverage-summary.mjs";
|
|
|
|
const DEFAULT_BASE_URL = "https://salesintelligence-dv.oracle.com";
|
|
const DEFAULT_WORKBOOK_URL =
|
|
"https://salesintelligence-dv.oracle.com/ui/dv/ui/project.jsp?pageid=visualAnalyzer&reportmode=presentation&reportpath=%2F%40Catalog%2Fshared%2FTeam%20Sharing%20Area%2FLAD%2FLAD%20Sales%20Operations%2FLAD%20Production%2FLAD%20Knowledge%20One%20View";
|
|
const SQL_ENDPOINT = "/ui/dv/ui/api/v1/sqlquery/execute";
|
|
const SESSION_INFO_ENDPOINT = "/ui/dv/ui/api/v1/sessioninfo/ext";
|
|
const NULL_SENTINEL = "__CODEX_NULL__";
|
|
const VARCHAR_SIZE = 512;
|
|
const FISCAL_YEAR_CACHE_MAX_AGE_MS = 24 * 60 * 60 * 1000;
|
|
const SESSION_CONTEXT_CACHE_MAX_AGE_MS = 6 * 60 * 60 * 1000;
|
|
const SA_ATTACH_OPPORTUNITIES_DATASET = `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Opportunities - V2')`;
|
|
const SA_ATTACH_HOURS_DATASET = `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Hours - V2')`;
|
|
const SA_ATTACH_PRESALES_AUX_DATASET = `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Presales Involvement Aux - V1')`;
|
|
const SA_ATTACH_REVENUE_LINES_DATASET = `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')`;
|
|
const SOURCE_OPPORTUNITIES = "Knowledge DV - Opportunities - V2";
|
|
const SOURCE_HOURS = "Knowledge DV - Hours - V2";
|
|
const SOURCE_PRESALES_AUX = "Knowledge DV - Presales Involvement Aux - V1";
|
|
const SOURCE_REVENUE_LINES = "Knowledge DV - Revn Lines by SE";
|
|
const SOURCE_DV_SE_TEAM = "DV - SE Team";
|
|
const SCOPE_AUTO = "auto";
|
|
const SCOPE_ORG_WIDE = "org-wide";
|
|
const SCOPE_MY_TEAM = "my-team-only";
|
|
const SCOPE_MIXED = "mixed-consistent";
|
|
const SCOPE_UNKNOWN = "unknown";
|
|
const BACKEND_ORG_WIDE = "org-wide";
|
|
const BACKEND_ORG_WIDE_CAPABLE = "org-wide-capable";
|
|
const BACKEND_MY_TEAM_ONLY = "my-team-only";
|
|
const DATASET_REGISTRY = {
|
|
[SOURCE_OPPORTUNITIES]: {
|
|
sourceName: SOURCE_OPPORTUNITIES,
|
|
subjectArea: SA_ATTACH_OPPORTUNITIES_DATASET,
|
|
backendCapability: BACKEND_ORG_WIDE,
|
|
defaultScope: SCOPE_ORG_WIDE,
|
|
allowedScopes: [SCOPE_ORG_WIDE],
|
|
},
|
|
[SOURCE_HOURS]: {
|
|
sourceName: SOURCE_HOURS,
|
|
subjectArea: SA_ATTACH_HOURS_DATASET,
|
|
backendCapability: BACKEND_ORG_WIDE_CAPABLE,
|
|
defaultScope: SCOPE_ORG_WIDE,
|
|
allowedScopes: [SCOPE_ORG_WIDE, SCOPE_MY_TEAM],
|
|
},
|
|
[SOURCE_PRESALES_AUX]: {
|
|
sourceName: SOURCE_PRESALES_AUX,
|
|
subjectArea: SA_ATTACH_PRESALES_AUX_DATASET,
|
|
backendCapability: BACKEND_ORG_WIDE_CAPABLE,
|
|
defaultScope: SCOPE_ORG_WIDE,
|
|
allowedScopes: [SCOPE_ORG_WIDE, SCOPE_MY_TEAM],
|
|
},
|
|
[SOURCE_REVENUE_LINES]: {
|
|
sourceName: SOURCE_REVENUE_LINES,
|
|
subjectArea: SA_ATTACH_REVENUE_LINES_DATASET,
|
|
backendCapability: BACKEND_ORG_WIDE_CAPABLE,
|
|
defaultScope: SCOPE_ORG_WIDE,
|
|
allowedScopes: [SCOPE_ORG_WIDE, SCOPE_MY_TEAM],
|
|
},
|
|
[SOURCE_DV_SE_TEAM]: {
|
|
sourceName: SOURCE_DV_SE_TEAM,
|
|
subjectArea: `"${SOURCE_DV_SE_TEAM}"`,
|
|
backendCapability: BACKEND_MY_TEAM_ONLY,
|
|
defaultScope: SCOPE_MY_TEAM,
|
|
allowedScopes: [SCOPE_MY_TEAM],
|
|
},
|
|
};
|
|
const VIEW_SCOPE_REGISTRY = {
|
|
"current-fiscal-year": { sourceName: SOURCE_DV_SE_TEAM, requiresMyTeam: true },
|
|
"data-quality-session-context": { sourceName: SOURCE_DV_SE_TEAM, requiresMyTeam: true },
|
|
"effort-by-se-team": { sourceName: SOURCE_DV_SE_TEAM, requiresMyTeam: true },
|
|
"effort-task-type-summary": { sourceName: SOURCE_DV_SE_TEAM, requiresMyTeam: true },
|
|
"opportunities-close-window": { sourceName: SOURCE_OPPORTUNITIES },
|
|
"opportunity-details": { sourceName: SOURCE_OPPORTUNITIES },
|
|
"revenue-line-details": { sourceName: SOURCE_REVENUE_LINES },
|
|
"sa-attach-hours-by-opportunity": { sourceName: SOURCE_HOURS },
|
|
"sa-attach-involvement-distribution": { sourceName: SOURCE_OPPORTUNITIES },
|
|
"sa-attach-open-pipeline": { sourceName: SOURCE_OPPORTUNITIES },
|
|
"sa-attach-pipeline-details": { sourceName: SOURCE_OPPORTUNITIES },
|
|
"sa-attach-presales-coverage": { sourceName: SOURCE_PRESALES_AUX },
|
|
"sa-attach-won-pipeline": { sourceName: SOURCE_OPPORTUNITIES },
|
|
"sr-details": { sourceName: SOURCE_DV_SE_TEAM, requiresMyTeam: true },
|
|
"srs-by-resource-email": { sourceName: SOURCE_DV_SE_TEAM, requiresMyTeam: true },
|
|
"won-and-open-by-se-opty": { sourceName: SOURCE_DV_SE_TEAM, requiresMyTeam: true },
|
|
};
|
|
const OPPORTUNITY_DETAILS_COLUMNS = [
|
|
{ name: "opportunityId", expr: '"Opportunity"."Opportunity ID"', kind: "text" },
|
|
{ name: "opportunityHours", expr: '"Effort"."# Opportunity Hours"', kind: "scalar" },
|
|
{ name: "opportunityStatus", expr: '"Opportunity"."Status"', kind: "text" },
|
|
{ name: "workloadAmount", expr: '"Opportunity"."Workload Amount"', kind: "scalar" },
|
|
{ name: "registryId", expr: '"Customer"."Registry ID"', kind: "text" },
|
|
{ name: "customerName", expr: '"Customer"."Customer Name"', kind: "text" },
|
|
{ name: "dealQualification", expr: '"Opportunity"."Deal Qualification"', kind: "text" },
|
|
{ name: "forecastTypeGroup", expr: '"Opportunity"."Forecast Type Group"', kind: "text" },
|
|
{ name: "bookingValue", expr: '"Opportunity"."Opportunity Value"', kind: "scalar" },
|
|
{ name: "optyCloseFiscalQuarter", expr: '"Time (Close Date Opportunity)"."Opty Close Fiscal Quarter"', kind: "text" },
|
|
{ name: "sesWithEffort", expr: '"Effort"."# SEs With Effort"', kind: "scalar" },
|
|
{ name: "serviceRequestsWithEffort", expr: '"Effort"."# Service Requests with Effort"', kind: "scalar" },
|
|
{ name: "country", expr: '"Customer"."Country"', kind: "text" },
|
|
{ name: "salesStage", expr: '"Opportunity"."Sales Stage"', kind: "text" },
|
|
];
|
|
|
|
const SR_DETAILS_COLUMNS = [
|
|
{ name: "opportunityId", expr: '"Opportunity"."Opportunity ID"', kind: "text" },
|
|
{ name: "opportunityStatus", expr: '"Opportunity"."Status"', kind: "text" },
|
|
{ name: "workloadAmount", expr: '"Opportunity"."Workload Amount"', kind: "scalar" },
|
|
{ name: "registryId", expr: '"Customer"."Registry ID"', kind: "text" },
|
|
{ name: "customerName", expr: '"Customer"."Customer Name"', kind: "text" },
|
|
{ name: "bookingValue", expr: '"Opportunity"."Opportunity Value"', kind: "scalar" },
|
|
{ name: "sesWithEffort", expr: '"Effort"."# SEs With Effort"', kind: "scalar" },
|
|
{ name: "serviceRequestsWithEffort", expr: '"Effort"."# Service Requests with Effort"', kind: "scalar" },
|
|
{ name: "srSource", expr: '"Service Request"."SR Source"', kind: "text" },
|
|
{ name: "srStatus", expr: '"Service Request"."Status"', kind: "text" },
|
|
{ name: "country", expr: '"Customer"."Country"', kind: "text" },
|
|
{ name: "hoursExcludingTimeOff", expr: '"Effort"."# Hours (Excl. Time Off)"', kind: "scalar" },
|
|
{ name: "srNumber", expr: '"Service Request"."SR Number"', kind: "text" },
|
|
{ name: "createSrDay", expr: '"Time (Create Date Service Request)"."Create SR Day"', kind: "scalar" },
|
|
{ name: "lastEffort", expr: '"Service Request"."Last Effort"', kind: "scalar" },
|
|
{ name: "leadEmail", expr: '"Service Request"."Lead Email"', kind: "text" },
|
|
{ name: "customerDeadline", expr: '"Service Request"."Customer Deadline"', kind: "scalar" },
|
|
{ name: "primaryPillar", expr: '"Service Request"."Primary Pillar"', kind: "text" },
|
|
{ name: "opportunityName", expr: '"Opportunity"."Opportunity Name"', kind: "text" },
|
|
{ name: "totalSrHours", expr: '"Service Request"."Total SR Hours"', kind: "scalar" },
|
|
{ name: "title", expr: '"Service Request"."Title"', kind: "text" },
|
|
{ name: "queueName", expr: '"Service Request"."Queue Name"', kind: "text" },
|
|
{ name: "srRequesterEmailAddress", expr: '"SR Requester"."SR Requester Email Address"', kind: "text" },
|
|
{ name: "closeSrDay", expr: '"Time (Close Date Service Request)"."Close SR Day"', kind: "scalar" },
|
|
];
|
|
|
|
const WON_AND_OPEN_COLUMNS = [
|
|
{ name: "opportunityId", expr: '"Opportunity"."Opportunity ID"', kind: "text" },
|
|
{ name: "customerName", expr: '"Customer"."Customer Name"', kind: "text" },
|
|
{ name: "opportunityName", expr: '"Opportunity"."Opportunity Name"', kind: "text" },
|
|
{ name: "optyCloseFiscalQuarter", expr: '"Time (Close Date Opportunity)"."Opty Close Fiscal Quarter"', kind: "text" },
|
|
{ name: "optyCloseFiscalMonth", expr: '"Time (Close Date Opportunity)"."Opty Close Fiscal Month"', kind: "text" },
|
|
{ name: "workloadAmount", expr: '"Opportunity"."Workload Amount"', kind: "scalar" },
|
|
{ name: "seTeamLevel2EmailAddress", expr: '"SR Team"."SE Team Level 2 Email Address"', kind: "text" },
|
|
{ name: "opportunityHours", expr: '"Effort"."# Opportunity Hours"', kind: "scalar" },
|
|
{
|
|
name: "hoursContribution",
|
|
expr: '"Effort"."# Opportunity Hours" / (SUM("Effort"."# Opportunity Hours" BY "Opportunity"."Opportunity ID"))',
|
|
kind: "scalar",
|
|
},
|
|
{ name: "dealContribution", expr: '"Effort"."Deal Contribution"', kind: "scalar" },
|
|
];
|
|
|
|
const REVENUE_LINE_COLUMNS = [
|
|
{
|
|
name: "revenueLineId",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Revenue Line ID"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "opportunityId",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Opportunity ID"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "customerNameOriginal",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Customer Name (Original)"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "registryId",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Registry Id"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "solution",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Solution"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "revenueLineStatus",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Revenue Line Status"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "revenueTypeGroup",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Revenue Type Group"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "executiveProductLob",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Executive Product Lob"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "pipelineAmount",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Pipeline"`,
|
|
kind: "scalar",
|
|
},
|
|
{
|
|
name: "revenueLineCloseYear",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Revenue Line Close Year"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "revenueLineCloseQuarter",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Revenue Line Close Quarter"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "revenueLineCloseMonth",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Revenue Line Close Month"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "revenueLineCloseDate",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Revenue Line Close Date"`,
|
|
kind: "scalar",
|
|
},
|
|
{
|
|
name: "seTeamLevel2EmailAddress",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."SE Team Level 2 Email Address"`,
|
|
kind: "text",
|
|
},
|
|
{
|
|
name: "cluster",
|
|
expr: `XSA('ANNA.DE.MAURO@ORACLE.COM'.'Knowledge DV - Revn Lines by SE')."Columns"."Cluster"`,
|
|
kind: "text",
|
|
},
|
|
];
|
|
|
|
const DATA_QUALITY_CONTEXT_COLUMNS = [
|
|
{ name: "securityEntryPoint", expr: '"Security"."Entry Point"', kind: "text" },
|
|
{ name: "currentFiscalYear", expr: "valueof(RV_CURR_FISCAL_YEAR)", kind: "text" },
|
|
{ name: "currentFiscalWeek", expr: "valueof(RV_CURR_FISCAL_WEEK)", kind: "text" },
|
|
{ name: "previousFiscalWeek", expr: "valueof(RV_PREV_FISCAL_WEEK)", kind: "text" },
|
|
{ name: "previousPreviousFiscalWeek", expr: "valueof(RV_PREV_PREV_FISCAL_WEEK)", kind: "text" },
|
|
{ name: "previousPreviousPreviousFiscalWeek", expr: "valueof(RV_PREV_PREV_PREV_FISCAL_WEEK)", kind: "text" },
|
|
{ name: "nextFiscalYear", expr: "valueof(RV_NEXT_FISCAL_YEAR)", kind: "text" },
|
|
];
|
|
|
|
const EFFORT_TASK_TYPE_COLUMNS = [
|
|
{ name: "taskType", expr: '"Effort Details"."Task Type"', kind: "text" },
|
|
{ name: "hoursExcludingTimeOff", expr: '"Effort"."# Hours (Excl. Time Off)"', kind: "scalar" },
|
|
];
|
|
|
|
const EFFORT_BY_TEAM_COLUMNS = [
|
|
{ name: "seTeamLevel2EmailAddress", expr: '"SR Team"."SE Team Level 2 Email Address"', kind: "text" },
|
|
{ name: "totalHours", expr: '"Effort"."# Hours (Excl. Time Off)"', kind: "scalar" },
|
|
];
|
|
|
|
const OPPORTUNITIES_CLOSE_WINDOW_COLUMNS = [
|
|
{ name: "opportunityId", expr: '"Opportunity"."Opportunity ID"', kind: "text" },
|
|
{ name: "opportunityName", expr: '"Opportunity"."Opportunity Name"', kind: "text" },
|
|
{ name: "customerName", expr: '"Customer"."Customer Name"', kind: "text" },
|
|
{ name: "opportunityStatus", expr: '"Opportunity"."Status"', kind: "text" },
|
|
{ name: "closeDate", expr: '"Opportunity"."Close Date"', kind: "scalar" },
|
|
{ name: "salesStage", expr: '"Opportunity"."Sales Stage"', kind: "text" },
|
|
{ name: "bookingValue", expr: '"Opportunity"."Opportunity Value"', kind: "scalar" },
|
|
{ name: "workloadAmount", expr: '"Opportunity"."Workload Amount"', kind: "scalar" },
|
|
];
|
|
|
|
const SRS_BY_RESOURCE_COLUMNS = [
|
|
{ name: "srNumber", expr: '"Service Request"."SR Number"', kind: "text" },
|
|
{ name: "srStatus", expr: '"Service Request"."Status"', kind: "text" },
|
|
{ name: "title", expr: '"Service Request"."Title"', kind: "text" },
|
|
{ name: "srSource", expr: '"Service Request"."SR Source"', kind: "text" },
|
|
{ name: "serviceDescription", expr: '"Service Request"."Service Description"', kind: "text" },
|
|
{ name: "customerName", expr: '"Customer"."Customer Name"', kind: "text" },
|
|
{ name: "opportunityId", expr: '"Opportunity"."Opportunity ID"', kind: "text" },
|
|
{ name: "opportunityName", expr: '"Opportunity"."Opportunity Name"', kind: "text" },
|
|
{ name: "opportunityStatus", expr: '"Opportunity"."Status"', kind: "text" },
|
|
{ name: "primaryPillar", expr: '"Service Request"."Primary Pillar"', kind: "text" },
|
|
{ name: "leadEmail", expr: '"Service Request"."Lead Email"', kind: "text" },
|
|
{ name: "queueName", expr: '"Service Request"."Queue Name"', kind: "text" },
|
|
{ name: "internalDeadline", expr: '"Service Request"."Internal Deadline"', kind: "scalar" },
|
|
{ name: "bookingValue", expr: '"Opportunity"."Opportunity Value"', kind: "scalar" },
|
|
{ name: "workloadAmount", expr: '"Opportunity"."Workload Amount"', kind: "scalar" },
|
|
{ name: "totalSrHours", expr: '"Service Request"."Total SR Hours"', kind: "scalar" },
|
|
{ name: "lastEffort", expr: '"Service Request"."Last Effort"', kind: "scalar" },
|
|
];
|
|
|
|
const CURRENT_TEAM_CLOSE_WINDOW_COLUMNS = [
|
|
"opportunityId",
|
|
"opportunityName",
|
|
"customerName",
|
|
"opportunityStatus",
|
|
"closeDate",
|
|
"salesStage",
|
|
"bookingValue",
|
|
"workloadAmount",
|
|
"teamMemberEmails",
|
|
"currentTeamOpportunityHours",
|
|
"coverageRows",
|
|
];
|
|
|
|
const WORKLOAD_MANAGER_RESOURCE_CLOSE_WINDOW_COLUMNS = [
|
|
"closeDate",
|
|
"revenueLineId",
|
|
"opportunityId",
|
|
"opportunityName",
|
|
"customerName",
|
|
"workloadAmount",
|
|
"managerEmails",
|
|
"resourceEmails",
|
|
"resourceDetails",
|
|
];
|
|
|
|
const SQL_NULL_TEXT = `CAST(NULL AS VARCHAR(${VARCHAR_SIZE}))`;
|
|
const SQL_NULL_SCALAR = "CAST(NULL AS DOUBLE)";
|
|
|
|
const SA_ATTACH_PIPELINE_COLUMNS = [
|
|
{ name: "revenueLineId", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line ID"), kind: "text" },
|
|
{ name: "opportunityId", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity ID"), kind: "text" },
|
|
{ name: "customerNameOriginal", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Customer Name (Original)"), kind: "text" },
|
|
{ name: "solution", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Solution"), kind: "text" },
|
|
{ name: "pipelineAmount", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline"), kind: "scalar" },
|
|
{ name: "revenueLineStatus", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Status"), kind: "text" },
|
|
{ name: "revenueTypeGroup", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Type Group"), kind: "text" },
|
|
{ name: "salesCreditType", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Sales Credit Type"), kind: "text" },
|
|
{ name: "revenueLineProbability", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Probability"), kind: "scalar" },
|
|
{ name: "opportunityProbability", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Probability"), kind: "scalar" },
|
|
{ name: "presalesInvolvementFlag", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Presales Involvement Flag"), kind: "text" },
|
|
{ name: "actionToCloseFilledFlag", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Action to Close (Fusion)"), kind: "text" },
|
|
{ name: "managerReview", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Manager Review"), kind: "text" },
|
|
{ name: "executiveProductLob", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Executive Product Lob"), kind: "text" },
|
|
{ name: "executiveProductClass", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Executive Product Class"), kind: "text" },
|
|
{ name: "executiveProductPillar", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Executive Product Pillar"), kind: "text" },
|
|
{ name: "executiveProductLine", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Executive Product Line"), kind: "text" },
|
|
{ name: "productGroup", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Product Group"), kind: "text" },
|
|
{ name: "productTier4", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Product Tier 4"), kind: "text" },
|
|
{ name: "productTier5", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Product Tier 5"), kind: "text" },
|
|
{ name: "productTier6", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Product Tier 6"), kind: "text" },
|
|
{ name: "revenueLineForecastTypeGroup", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Forecast Type Group"), kind: "text" },
|
|
{ name: "saasPurchaseCategory", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "SaaS Purchase Category"), kind: "text" },
|
|
{ name: "revenueLineCloseYear", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Year"), kind: "text" },
|
|
{ name: "revenueLineCloseQuarter", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Quarter"), kind: "text" },
|
|
{ name: "revenueLineCloseMonth", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Month"), kind: "text" },
|
|
{ name: "revenueLineCloseDate", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Date"), kind: "scalar" },
|
|
{ name: "territoryName", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Territory Name"), kind: "text" },
|
|
{ name: "level2TerritoryOwnerEmail", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 2 Territory Owner E-mail"), kind: "text" },
|
|
{ name: "level3TerritoryOwnerEmail", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 3 Territory Owner E-mail"), kind: "text" },
|
|
{ name: "level4TerritoryOwnerEmail", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 4 Territory Owner E-mail"), kind: "text" },
|
|
{ name: "level5TerritoryOwnerEmail", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 5 Territory Owner E-mail"), kind: "text" },
|
|
{ name: "level6TerritoryOwnerEmail", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 6 Territory Owner E-mail"), kind: "text" },
|
|
{ name: "level7TerritoryOwnerEmail", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 7 Territory Owner E-mail"), kind: "text" },
|
|
{ name: "cluster", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Cluster"), kind: "text" },
|
|
{ name: "segment", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Segment"), kind: "text" },
|
|
{ name: "publicSectorFlag", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Public Sector Flag"), kind: "text" },
|
|
{ name: "telcoFlag", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Telco Flag"), kind: "text" },
|
|
{ name: "campaignSourceCode", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "All Campaign Source Code"), kind: "text" },
|
|
];
|
|
|
|
const WORKLOAD_MANAGER_RESOURCE_PIPELINE_COLUMNS = [
|
|
{ name: "closeDate", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Date"), kind: "scalar" },
|
|
{ name: "revenueLineId", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line ID"), kind: "text" },
|
|
{ name: "opportunityId", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity ID"), kind: "text" },
|
|
{ name: "opportunityName", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Name"), kind: "text" },
|
|
{
|
|
name: "customerName",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Customer Name (Original)"),
|
|
kind: "text",
|
|
},
|
|
{ name: "workloadAmount", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline"), kind: "scalar" },
|
|
];
|
|
|
|
const PRIMARY_OPPORTUNITY_WINDOW_COLUMNS = [
|
|
{ name: "opportunityId", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity ID"), kind: "text" },
|
|
{ name: "opportunityName", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Name"), kind: "text" },
|
|
{
|
|
name: "customerName",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Customer Name (Original)"),
|
|
kind: "text",
|
|
},
|
|
{ name: "opportunityStatus", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Status"), kind: "text" },
|
|
{ name: "closeDate", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Date"), kind: "scalar" },
|
|
{ name: "salesStage", expr: "''", kind: "text" },
|
|
{
|
|
name: "bookingValue",
|
|
expr: `MAX(${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline")})`,
|
|
kind: "scalar",
|
|
},
|
|
{
|
|
name: "workloadAmount",
|
|
expr: `MAX(${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline")})`,
|
|
kind: "scalar",
|
|
},
|
|
];
|
|
|
|
const PRIMARY_OPPORTUNITY_DETAILS_COLUMNS = [
|
|
{ name: "opportunityId", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity ID"), kind: "text" },
|
|
{ name: "opportunityHours", expr: SQL_NULL_SCALAR, kind: "scalar" },
|
|
{ name: "opportunityStatus", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Status"), kind: "text" },
|
|
{
|
|
name: "workloadAmount",
|
|
expr: `MAX(${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Workload Amount")})`,
|
|
kind: "scalar",
|
|
},
|
|
{ name: "registryId", expr: SQL_NULL_TEXT, kind: "text" },
|
|
{
|
|
name: "customerName",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Customer Name (Original)"),
|
|
kind: "text",
|
|
},
|
|
{ name: "dealQualification", expr: SQL_NULL_TEXT, kind: "text" },
|
|
{ name: "forecastTypeGroup", expr: SQL_NULL_TEXT, kind: "text" },
|
|
{
|
|
name: "bookingValue",
|
|
expr: `MAX(${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Value")})`,
|
|
kind: "scalar",
|
|
},
|
|
{ name: "optyCloseFiscalQuarter", expr: SQL_NULL_TEXT, kind: "text" },
|
|
{ name: "sesWithEffort", expr: SQL_NULL_SCALAR, kind: "scalar" },
|
|
{ name: "serviceRequestsWithEffort", expr: SQL_NULL_SCALAR, kind: "scalar" },
|
|
{ name: "country", expr: SQL_NULL_TEXT, kind: "text" },
|
|
{ name: "salesStage", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Sales Stage"), kind: "text" },
|
|
];
|
|
|
|
const SA_ATTACH_METRIC_COLUMNS = [
|
|
{ name: "pipelineAmount", expr: `SUM(${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline")})`, kind: "scalar" },
|
|
];
|
|
|
|
const SA_ATTACH_INVOLVEMENT_DISTRIBUTION_COLUMNS = [
|
|
{ name: "involvementBucket", expr: buildSaAttachInvolvementBucketExpr(), kind: "text" },
|
|
{ name: "revenueLineProbability", expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Probability"), kind: "scalar" },
|
|
{ name: "pipelineAmount", expr: `SUM(${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline")})`, kind: "scalar" },
|
|
];
|
|
|
|
const SA_ATTACH_HOURS_COLUMNS = [
|
|
{ name: "opportunityId", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "Opportunity ID"), kind: "text" },
|
|
{ name: "seTeamLevel1EmailAddress", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "SE Team Level 1 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel2EmailAddress", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "SE Team Level 2 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel3EmailAddress", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "SE Team Level 3 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel4EmailAddress", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "SE Team Level 4 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel5EmailAddress", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "SE Team Level 5 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel6EmailAddress", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "SE Team Level 6 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel7EmailAddress", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "SE Team Level 7 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel8EmailAddress", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "SE Team Level 8 Email Address"), kind: "text" },
|
|
{ name: "opportunityHours", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "# Opportunity Hours"), kind: "scalar" },
|
|
{ name: "wonFlag", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "Won Flag"), kind: "text" },
|
|
{ name: "openFlag", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "Open Flag"), kind: "text" },
|
|
{ name: "cloudApplicationsBookingsFlag", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "Cloud Applications - Bookings Flag"), kind: "text" },
|
|
{ name: "cloudInfrastructureBookingsFlag", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "Cloud Infrastructure - Bookings Flag"), kind: "text" },
|
|
{ name: "cloudInfrastructureWorkloadsFlag", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "Cloud Infrastructure - Workloads Flag"), kind: "text" },
|
|
{ name: "onPremiseApplicationsBookingsFlag", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "On-Premise Applications - Bookings Flag"), kind: "text" },
|
|
{ name: "onPremiseTechnologyBookingsFlag", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "On-Premise Technology - Bookings Flag"), kind: "text" },
|
|
{ name: "hardwareBookingsFlag", expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "Hardware - Bookings Flag"), kind: "text" },
|
|
];
|
|
|
|
const SA_ATTACH_PRESALES_AUX_COLUMNS = [
|
|
{ name: "opportunityId", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "Opportunity ID"), kind: "text" },
|
|
{ name: "seTeamLevel1EmailAddress", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 1 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel2EmailAddress", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 2 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel3EmailAddress", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 3 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel4EmailAddress", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 4 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel5EmailAddress", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 5 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel6EmailAddress", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 6 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel7EmailAddress", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 7 Email Address"), kind: "text" },
|
|
{ name: "seTeamLevel8EmailAddress", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 8 Email Address"), kind: "text" },
|
|
{ name: "opportunityHours", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "# Opportunity Hours"), kind: "scalar" },
|
|
{ name: "opportunityHoursAttribute", expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "Opportunity Hours Attribute"), kind: "scalar" },
|
|
];
|
|
|
|
const FIELD_CATALOG = [
|
|
{
|
|
alias: "opportunityId",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Opportunity ID"',
|
|
kind: "text",
|
|
roles: ["select", "order"],
|
|
description: "Stable Oracle opportunity identifier.",
|
|
},
|
|
{
|
|
alias: "opportunityName",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Opportunity Name"',
|
|
kind: "text",
|
|
roles: ["select", "search"],
|
|
description: "Opportunity name shown in the workbook.",
|
|
},
|
|
{
|
|
alias: "opportunityStatus",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Status"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Opportunity lifecycle status such as Open, Won, Closed, or Won Pending.",
|
|
},
|
|
{
|
|
alias: "closeDate",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Close Date"',
|
|
kind: "date",
|
|
roles: ["select", "filter", "order"],
|
|
description:
|
|
"Close-date alias. In org-wide Knowledge DV - Opportunities - V2 queries this resolves to Revenue Line Close Date; filter with DATE literals such as DATE '2026-03-27'.",
|
|
},
|
|
{
|
|
alias: "salesStage",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Sales Stage"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Sales stage label such as 1. Engage or 6. Close.",
|
|
},
|
|
{
|
|
alias: "opportunityValue",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Opportunity Value"',
|
|
kind: "scalar",
|
|
roles: ["select", "filter", "aggregate"],
|
|
description: "Opportunity value measure. In pipeline-style outputs, treat this as bookingValue.",
|
|
},
|
|
{
|
|
alias: "bookingValue",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Opportunity Value"',
|
|
kind: "scalar",
|
|
roles: ["select", "filter", "aggregate"],
|
|
description: "Booking value alias backed by \"Opportunity\".\"Opportunity Value\".",
|
|
},
|
|
{
|
|
alias: "workloadAmount",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Workload Amount"',
|
|
kind: "scalar",
|
|
roles: ["select", "filter", "aggregate"],
|
|
description: "Workload amount measure commonly used in pipeline views.",
|
|
},
|
|
{
|
|
alias: "dealQualification",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Deal Qualification"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Deal qualification attribute from the opportunity entity.",
|
|
},
|
|
{
|
|
alias: "forecastTypeGroup",
|
|
entity: "Opportunity",
|
|
expr: '"Opportunity"."Forecast Type Group"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Forecast grouping used in workbook slices.",
|
|
},
|
|
{
|
|
alias: "customerName",
|
|
entity: "Customer",
|
|
expr: '"Customer"."Customer Name"',
|
|
kind: "text",
|
|
roles: ["select", "filter", "search"],
|
|
description: "Customer account display name.",
|
|
},
|
|
{
|
|
alias: "registryId",
|
|
entity: "Customer",
|
|
expr: '"Customer"."Registry ID"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Customer registry identifier.",
|
|
},
|
|
{
|
|
alias: "country",
|
|
entity: "Customer",
|
|
expr: '"Customer"."Country"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Customer country.",
|
|
},
|
|
{
|
|
alias: "securityEntryPoint",
|
|
entity: "Security",
|
|
expr: '"Security"."Entry Point"',
|
|
kind: "text",
|
|
roles: ["filter"],
|
|
description: "Security entry point. Usually filter with VALUEOF(NQ_SESSION.\"SEC_ARIA_SI-SR-TEAM\").",
|
|
},
|
|
{
|
|
alias: "optyCloseFiscalYear",
|
|
entity: "Time (Close Date Opportunity)",
|
|
expr: '"Time (Close Date Opportunity)"."Opty Close Fiscal Year"',
|
|
kind: "text",
|
|
roles: ["filter", "date-part"],
|
|
description: "Opportunity close fiscal year bucket used by the default workbook filters.",
|
|
},
|
|
{
|
|
alias: "optyCloseFiscalQuarter",
|
|
entity: "Time (Close Date Opportunity)",
|
|
expr: '"Time (Close Date Opportunity)"."Opty Close Fiscal Quarter"',
|
|
kind: "text",
|
|
roles: ["select", "filter", "date-part"],
|
|
description: "Opportunity close fiscal quarter.",
|
|
},
|
|
{
|
|
alias: "optyCloseFiscalMonth",
|
|
entity: "Time (Close Date Opportunity)",
|
|
expr: '"Time (Close Date Opportunity)"."Opty Close Fiscal Month"',
|
|
kind: "text",
|
|
roles: ["select", "filter", "date-part"],
|
|
description: "Opportunity close fiscal month.",
|
|
},
|
|
{
|
|
alias: "srNumber",
|
|
entity: "Service Request",
|
|
expr: '"Service Request"."SR Number"',
|
|
kind: "text",
|
|
roles: ["select", "filter", "order"],
|
|
description: "Service request number.",
|
|
},
|
|
{
|
|
alias: "srStatus",
|
|
entity: "Service Request",
|
|
expr: '"Service Request"."Status"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Service request status.",
|
|
},
|
|
{
|
|
alias: "srSource",
|
|
entity: "Service Request",
|
|
expr: '"Service Request"."SR Source"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Service request source such as Opportunity.",
|
|
},
|
|
{
|
|
alias: "title",
|
|
entity: "Service Request",
|
|
expr: '"Service Request"."Title"',
|
|
kind: "text",
|
|
roles: ["select", "search"],
|
|
description: "Service request title.",
|
|
},
|
|
{
|
|
alias: "queueName",
|
|
entity: "Service Request",
|
|
expr: '"Service Request"."Queue Name"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Service request queue name.",
|
|
},
|
|
{
|
|
alias: "primaryPillar",
|
|
entity: "Service Request",
|
|
expr: '"Service Request"."Primary Pillar"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Primary pillar classification for the SR.",
|
|
},
|
|
{
|
|
alias: "leadEmail",
|
|
entity: "Service Request",
|
|
expr: '"Service Request"."Lead Email"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Lead email on the SR. Use only when the intent is specifically to find the SR leader or opportunity leader.",
|
|
},
|
|
{
|
|
alias: "srTeamLevel1EmailAddress",
|
|
entity: "SR Team",
|
|
expr: '"SR Team"."SE Team Level 1 Email Address"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SR team level 1 member email. Use for participant/resource matching.",
|
|
},
|
|
{
|
|
alias: "srTeamLevel2EmailAddress",
|
|
entity: "SR Team",
|
|
expr: '"SR Team"."SE Team Level 2 Email Address"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SR team level 2 member email. Use for participant/resource matching.",
|
|
},
|
|
{
|
|
alias: "srTeamLevel3EmailAddress",
|
|
entity: "SR Team",
|
|
expr: '"SR Team"."SE Team Level 3 Email Address"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SR team level 3 member email. Use for participant/resource matching.",
|
|
},
|
|
{
|
|
alias: "srTeamLevel4EmailAddress",
|
|
entity: "SR Team",
|
|
expr: '"SR Team"."SE Team Level 4 Email Address"',
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SR team level 4 member email. Use for participant/resource matching.",
|
|
},
|
|
{
|
|
alias: "customerDeadline",
|
|
entity: "Service Request",
|
|
expr: '"Service Request"."Customer Deadline"',
|
|
kind: "date",
|
|
roles: ["select", "filter"],
|
|
description: "Customer deadline date from the SR entity.",
|
|
},
|
|
{
|
|
alias: "createSrDay",
|
|
entity: "Time (Create Date Service Request)",
|
|
expr: '"Time (Create Date Service Request)"."Create SR Day"',
|
|
kind: "date",
|
|
roles: ["select", "filter", "date-part"],
|
|
description: "SR creation day.",
|
|
},
|
|
{
|
|
alias: "closeSrDay",
|
|
entity: "Time (Close Date Service Request)",
|
|
expr: '"Time (Close Date Service Request)"."Close SR Day"',
|
|
kind: "date",
|
|
roles: ["select", "filter", "date-part"],
|
|
description: "SR close day.",
|
|
},
|
|
{
|
|
alias: "opportunityHours",
|
|
entity: "Effort",
|
|
expr: '"Effort"."# Opportunity Hours"',
|
|
kind: "scalar",
|
|
roles: ["select", "aggregate", "filter"],
|
|
description: "Opportunity effort hours measure.",
|
|
},
|
|
{
|
|
alias: "hoursExcludingTimeOff",
|
|
entity: "Effort",
|
|
expr: '"Effort"."# Hours (Excl. Time Off)"',
|
|
kind: "scalar",
|
|
roles: ["select", "aggregate", "filter"],
|
|
description: "Effort hours excluding time off.",
|
|
},
|
|
{
|
|
alias: "serviceRequestsWithEffort",
|
|
entity: "Effort",
|
|
expr: '"Effort"."# Service Requests with Effort"',
|
|
kind: "scalar",
|
|
roles: ["select", "aggregate"],
|
|
description: "Count of service requests with recorded effort.",
|
|
},
|
|
{
|
|
alias: "sesWithEffort",
|
|
entity: "Effort",
|
|
expr: '"Effort"."# SEs With Effort"',
|
|
kind: "scalar",
|
|
roles: ["select", "aggregate"],
|
|
description: "Count of SEs with effort.",
|
|
},
|
|
{
|
|
alias: "pipelineAmount",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline"),
|
|
kind: "scalar",
|
|
roles: ["select", "aggregate", "filter"],
|
|
description: "Pipeline measure used by SA Attach and revenue-line style views.",
|
|
},
|
|
{
|
|
alias: "revenueLineId",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line ID"),
|
|
kind: "text",
|
|
roles: ["select", "order"],
|
|
description: "Revenue line identifier from the SA Attach opportunity dataset.",
|
|
},
|
|
{
|
|
alias: "revenueLineStatus",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Status"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Revenue line lifecycle status such as Open, Won, or Won Pending.",
|
|
},
|
|
{
|
|
alias: "revenueLineProbability",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Probability"),
|
|
kind: "scalar",
|
|
roles: ["select", "filter", "aggregate"],
|
|
description: "Revenue line probability used in the SA Attach involvement distribution.",
|
|
},
|
|
{
|
|
alias: "revenueTypeGroup",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Type Group"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Revenue type group such as NEW, EXPANSION, or WORKLOAD.",
|
|
},
|
|
{
|
|
alias: "salesCreditType",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Sales Credit Type"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Sales credit type such as QUOTA.",
|
|
},
|
|
{
|
|
alias: "solution",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Solution"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Normalized SA Attach solution family.",
|
|
},
|
|
{
|
|
alias: "executiveProductLob",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Executive Product Lob"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Executive product LOB used by SA Attach defaults.",
|
|
},
|
|
{
|
|
alias: "executiveProductClass",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Executive Product Class"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Executive product class from the SA Attach opportunity dataset.",
|
|
},
|
|
{
|
|
alias: "executiveProductPillar",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Executive Product Pillar"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Executive product pillar from the SA Attach opportunity dataset.",
|
|
},
|
|
{
|
|
alias: "executiveProductLine",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Executive Product Line"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Executive product line from the SA Attach opportunity dataset.",
|
|
},
|
|
{
|
|
alias: "productGroup",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Product Group"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Product group filter exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "productTier4",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Product Tier 4"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Product tier 4 filter exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "productTier5",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Product Tier 5"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Product tier 5 filter exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "productTier6",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Product Tier 6"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Product tier 6 filter exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "revenueLineForecastTypeGroup",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Forecast Type Group"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Revenue line forecast type group from the SA Attach opportunity dataset.",
|
|
},
|
|
{
|
|
alias: "saasPurchaseCategory",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "SaaS Purchase Category"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SaaS purchase category exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "revenueLineCloseDate",
|
|
entity: "Revenue Line",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Date"),
|
|
kind: "date",
|
|
roles: ["select", "filter", "order", "date-part"],
|
|
description: "Daily revenue-line close date from the SA Attach opportunity dataset.",
|
|
},
|
|
{
|
|
alias: "opportunityProbability",
|
|
entity: "Opportunity",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Probability"),
|
|
kind: "scalar",
|
|
roles: ["select", "filter", "aggregate"],
|
|
description: "Opportunity probability used by the SA Attach tab defaults.",
|
|
},
|
|
{
|
|
alias: "presalesInvolvementFlag",
|
|
entity: "Opportunity",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Presales Involvement Flag"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Presales involvement flag from the SA Attach opportunity dataset.",
|
|
},
|
|
{
|
|
alias: "actionToCloseFilledFlag",
|
|
entity: "Opportunity",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Action to Close (Fusion)"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Action-to-close flag exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "managerReview",
|
|
entity: "Opportunity",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Manager Review"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Manager review state exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "territoryName",
|
|
entity: "Territory",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Territory Name"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Territory name from the SA Attach opportunity dataset.",
|
|
},
|
|
{
|
|
alias: "territoryOwnerEmail",
|
|
entity: "Territory",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 7 Territory Owner E-mail"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Direct territory-owner email on the SA Attach opportunity dataset.",
|
|
},
|
|
{
|
|
alias: "cluster",
|
|
entity: "Territory",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Cluster"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Cluster filter exposed on SA Attach. The traced default is Brazil.",
|
|
},
|
|
{
|
|
alias: "segment",
|
|
entity: "Territory",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Segment"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Segment filter exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "publicSectorFlag",
|
|
entity: "Territory",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Public Sector Flag"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Public sector flag exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "telcoFlag",
|
|
entity: "Territory",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Telco Flag"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Telco flag exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "campaignSourceCode",
|
|
entity: "Campaign",
|
|
expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "All Campaign Source Code"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Campaign source code filter exposed on the SA Attach tab.",
|
|
},
|
|
{
|
|
alias: "seTeamLevel1EmailAddress",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 1 Email Address"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SA Attach presales coverage level 1 email. Useful for explicit manager/resource matching.",
|
|
},
|
|
{
|
|
alias: "seTeamLevel2EmailAddress",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 2 Email Address"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SA Attach presales coverage level 2 email. Useful for explicit manager/resource matching.",
|
|
},
|
|
{
|
|
alias: "seTeamLevel3EmailAddress",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 3 Email Address"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SA Attach presales coverage level 3 email. Useful for explicit manager/resource matching.",
|
|
},
|
|
{
|
|
alias: "seTeamLevel4EmailAddress",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 4 Email Address"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SA Attach presales coverage level 4 email. Useful for explicit manager/resource matching.",
|
|
},
|
|
{
|
|
alias: "seTeamLevel5EmailAddress",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 5 Email Address"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SA Attach presales coverage level 5 email. Useful for explicit manager/resource matching.",
|
|
},
|
|
{
|
|
alias: "seTeamLevel6EmailAddress",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 6 Email Address"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SA Attach presales coverage level 6 email. Useful for explicit manager/resource matching.",
|
|
},
|
|
{
|
|
alias: "seTeamLevel7EmailAddress",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 7 Email Address"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SA Attach presales coverage level 7 email. Useful for explicit manager/resource matching.",
|
|
},
|
|
{
|
|
alias: "seTeamLevel8EmailAddress",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 8 Email Address"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "SA Attach presales coverage level 8 email. Useful for explicit manager/resource matching.",
|
|
},
|
|
{
|
|
alias: "opportunityHoursAttribute",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "Opportunity Hours Attribute"),
|
|
kind: "scalar",
|
|
roles: ["select", "aggregate", "filter"],
|
|
description: "Opportunity hours attribute used by the SA Attach my-team involvement formula.",
|
|
},
|
|
{
|
|
alias: "wonFlag",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "Won Flag"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Won flag from the SA Attach hours dataset.",
|
|
},
|
|
{
|
|
alias: "openFlag",
|
|
entity: "Presales Involvement",
|
|
expr: xsaColumn(SA_ATTACH_HOURS_DATASET, "Open Flag"),
|
|
kind: "text",
|
|
roles: ["select", "filter"],
|
|
description: "Open flag from the SA Attach hours dataset.",
|
|
},
|
|
];
|
|
|
|
const QUERY_ORG_WIDE_FIELD_OVERRIDES = {
|
|
opportunityId: { expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity ID") },
|
|
opportunityName: { expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Name") },
|
|
opportunityStatus: { expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Status") },
|
|
closeDate: { expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Date") },
|
|
salesStage: { expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Sales Stage") },
|
|
opportunityValue: { expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline") },
|
|
bookingValue: { expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline") },
|
|
workloadAmount: { expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Pipeline") },
|
|
customerName: { expr: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Customer Name (Original)") },
|
|
};
|
|
const QUERY_MY_TEAM_ONLY_ALIAS_MARKERS = new Set([
|
|
"srnumber",
|
|
"srstatus",
|
|
"srsource",
|
|
"title",
|
|
"queuename",
|
|
"primarypillar",
|
|
"leademail",
|
|
"srteamlevel1emailaddress",
|
|
"srteamlevel2emailaddress",
|
|
"srteamlevel3emailaddress",
|
|
"srteamlevel4emailaddress",
|
|
"customerdeadline",
|
|
"createsrday",
|
|
"closesrday",
|
|
"securityentrypoint",
|
|
"hoursexcludingtimeoff",
|
|
"servicerequests",
|
|
]);
|
|
|
|
function buildSaAttachParameters(defaultStatuses = SA_ATTACH_DEFAULT_ALL_STATUSES) {
|
|
return [
|
|
{
|
|
name: "cluster",
|
|
cli: "--cluster",
|
|
required: false,
|
|
default: SA_ATTACH_DEFAULT_CLUSTER,
|
|
description: "Cluster filter. Defaults to Brazil to match the traced SA Attach tab.",
|
|
},
|
|
{
|
|
name: "solution",
|
|
cli: "--solution",
|
|
required: false,
|
|
default: SA_ATTACH_DEFAULT_SOLUTIONS.join(","),
|
|
description: "Optional comma-separated solution filter for SA Attach pipeline views.",
|
|
},
|
|
{
|
|
name: "revenueLineStatus",
|
|
cli: "--revenue-line-status",
|
|
required: false,
|
|
default: defaultStatuses.join(","),
|
|
description: "Optional comma-separated revenue-line statuses such as Open,Won,Won Pending.",
|
|
},
|
|
{
|
|
name: "executiveProductLob",
|
|
cli: "--executive-product-lob",
|
|
required: false,
|
|
default: SA_ATTACH_DEFAULT_PRODUCT_LOBS.join(","),
|
|
description: "Optional comma-separated executive product LOB filter.",
|
|
},
|
|
{
|
|
name: "revenueTypeGroup",
|
|
cli: "--revenue-type-group",
|
|
required: false,
|
|
default: SA_ATTACH_DEFAULT_REVENUE_TYPE_GROUPS.join(","),
|
|
description: "Optional comma-separated revenue type groups such as NEW,EXPANSION,WORKLOAD.",
|
|
},
|
|
{
|
|
name: "salesCreditType",
|
|
cli: "--sales-credit-type",
|
|
required: false,
|
|
default: SA_ATTACH_DEFAULT_SALES_CREDIT_TYPE,
|
|
description: "Sales credit type. Defaults to QUOTA to match the traced SA Attach tab.",
|
|
},
|
|
{
|
|
name: "minOpportunityProbability",
|
|
cli: "--min-opportunity-probability",
|
|
required: false,
|
|
default: String(SA_ATTACH_DEFAULT_MIN_OPPORTUNITY_PROBABILITY),
|
|
description: "Minimum opportunity probability. Defaults to 0.",
|
|
},
|
|
{
|
|
name: "ownerEmail",
|
|
cli: "--owner-email",
|
|
required: false,
|
|
description: "Optional territory-owner email filter. Matches the direct territory owner on the opportunity row.",
|
|
},
|
|
{
|
|
name: "ownerManagerEmail",
|
|
cli: "--owner-manager-email",
|
|
required: false,
|
|
description: "Optional manager-of-owner email filter. Matches upper territory-owner hierarchy levels on the opportunity row.",
|
|
},
|
|
{
|
|
name: "forecastType",
|
|
cli: "--forecast-type",
|
|
required: false,
|
|
description: "Optional forecast-type filter such as Forecast, Upside, or Won.",
|
|
},
|
|
{
|
|
name: "date",
|
|
cli: "--date",
|
|
required: false,
|
|
description: "Optional revenue-line close date shortcut for SA Attach reads.",
|
|
},
|
|
{
|
|
name: "month",
|
|
cli: "--month",
|
|
required: false,
|
|
description: "Optional revenue-line close month shortcut for SA Attach reads. Also accepts current-month.",
|
|
},
|
|
{
|
|
name: "quarter",
|
|
cli: "--quarter",
|
|
required: false,
|
|
description: "Optional revenue-line close quarter shortcut for SA Attach reads. Also accepts current-quarter.",
|
|
},
|
|
{
|
|
name: "week",
|
|
cli: "--week",
|
|
required: false,
|
|
description: "Optional revenue-line close week shortcut for SA Attach reads. Also accepts current-week.",
|
|
},
|
|
];
|
|
}
|
|
|
|
const VIEW_DEFS = {
|
|
"current-fiscal-year": {
|
|
description: "Returns the Oracle DV session fiscal year.",
|
|
entities: ["Session Variables"],
|
|
fields: ["currentFiscalYear"],
|
|
primarySource: SOURCE_DV_SE_TEAM,
|
|
buildSql: () => 'SELECT valueof(RV_CURR_FISCAL_YEAR) FROM "DV - SE Team"',
|
|
},
|
|
"opportunity-details": {
|
|
description:
|
|
"Opportunity extract that uses Knowledge DV - Opportunities - V2 as the primary commercial source for the org-wide hierarchy. DV - SE Team is only eligible as fallback for explicit current-team reads.",
|
|
entities: ["Opportunity", "Effort", "Customer", "Service Request", "SR Team", "Security"],
|
|
defaultStatuses: ["Open", "Won", "Won Pending"],
|
|
defaultLimit: 5000,
|
|
primarySource: SOURCE_OPPORTUNITIES,
|
|
fallbackSource: SOURCE_DV_SE_TEAM,
|
|
fallbackScope: "my-team-only",
|
|
fields: OPPORTUNITY_DETAILS_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ fiscalYear, statuses }) =>
|
|
buildDenseQuery({
|
|
columns: PRIMARY_OPPORTUNITY_DETAILS_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_OPPORTUNITIES_DATASET}`,
|
|
whereClauses: buildPrimaryOpportunityWhereClauses({ fiscalYear, statuses }),
|
|
groupBy: [
|
|
xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity ID"),
|
|
xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Status"),
|
|
xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Customer Name (Original)"),
|
|
xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Sales Stage"),
|
|
].join(", "),
|
|
orderBy: xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity ID"),
|
|
}),
|
|
buildFallbackSql: ({ fiscalYear, statuses }) =>
|
|
buildDenseQuery({
|
|
columns: OPPORTUNITY_DETAILS_COLUMNS,
|
|
fromClause: 'FROM "DV - SE Team"',
|
|
whereClauses: [
|
|
'"Security"."Entry Point" = VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM")',
|
|
`"Time (Close Date Opportunity)"."Opty Close Fiscal Year" = ${quoteLiteral(fiscalYear)}`,
|
|
`"Opportunity"."Status" IN (${toSqlList(statuses)})`,
|
|
`"SR Team"."SE Team Active Flag" = 'Y'`,
|
|
`"SR Team"."SE Team Manager Flag" = 'N'`,
|
|
`"Service Request"."SR Source" = 'Opportunity'`,
|
|
],
|
|
orderBy: '"Opportunity"."Opportunity ID"',
|
|
}),
|
|
},
|
|
"sr-details": {
|
|
description: "Row-level service request extract with the workbook defaults.",
|
|
entities: ["Service Request", "Opportunity", "Customer", "Effort", "SR Team", "SR Requester", "Security"],
|
|
defaultStatuses: ["Open"],
|
|
defaultLimit: 5000,
|
|
primarySource: SOURCE_DV_SE_TEAM,
|
|
fields: SR_DETAILS_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ fiscalYear, statuses }) =>
|
|
buildDenseQuery({
|
|
columns: SR_DETAILS_COLUMNS,
|
|
fromClause: 'FROM "DV - SE Team"',
|
|
whereClauses: [
|
|
'"Security"."Entry Point" = VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM")',
|
|
`"Time (Close Date Opportunity)"."Opty Close Fiscal Year" = ${quoteLiteral(fiscalYear)}`,
|
|
`"Opportunity"."Status" IN (${toSqlList(statuses)})`,
|
|
`"SR Team"."SE Team Active Flag" = 'Y'`,
|
|
`"SR Team"."SE Team Manager Flag" = 'N'`,
|
|
],
|
|
orderBy: '"Service Request"."SR Number"',
|
|
}),
|
|
},
|
|
"revenue-line-details": {
|
|
description: "Row-level revenue-line extract from the XSA revenue dataset.",
|
|
entities: ["Revenue Line", "Opportunity", "Customer", "SR Team"],
|
|
defaultStatuses: ["Open", "Won", "Won Pending"],
|
|
defaultLimit: 5000,
|
|
primarySource: SOURCE_REVENUE_LINES,
|
|
fields: REVENUE_LINE_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ fiscalYear, previousFiscalYear, statuses, scope = SCOPE_ORG_WIDE }) =>
|
|
buildDenseQuery({
|
|
columns: REVENUE_LINE_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_REVENUE_LINES_DATASET}`,
|
|
whereClauses: [
|
|
...buildXsaScopePredicates(SA_ATTACH_REVENUE_LINES_DATASET, scope),
|
|
`${xsaColumn(SA_ATTACH_REVENUE_LINES_DATASET, "Cluster")} = 'Brazil'`,
|
|
`${xsaColumn(SA_ATTACH_REVENUE_LINES_DATASET, "Sales Credit Type")} = 'QUOTA'`,
|
|
`${xsaColumn(SA_ATTACH_REVENUE_LINES_DATASET, "Revenue Line Status")} IN (${toSqlList(statuses)})`,
|
|
`(
|
|
${xsaColumn(SA_ATTACH_REVENUE_LINES_DATASET, "Revenue Line Status")} = 'Open'
|
|
OR ${xsaColumn(SA_ATTACH_REVENUE_LINES_DATASET, "Revenue Line Close Year")} IN (${toSqlList(
|
|
[previousFiscalYear, fiscalYear].filter(Boolean)
|
|
)})
|
|
)`,
|
|
],
|
|
orderBy: xsaColumn(SA_ATTACH_REVENUE_LINES_DATASET, "Revenue Line ID"),
|
|
}),
|
|
},
|
|
"sa-attach-pipeline-details": {
|
|
description: "Row-level SA Attach pipeline extract from Knowledge DV - Opportunities - V2 with the traced tab defaults.",
|
|
entities: ["Revenue Line", "Opportunity", "Customer", "Territory", "Campaign"],
|
|
defaultStatuses: SA_ATTACH_DEFAULT_ALL_STATUSES,
|
|
defaultLimit: 5000,
|
|
primarySource: SOURCE_OPPORTUNITIES,
|
|
fields: SA_ATTACH_PIPELINE_COLUMNS.map((column) => column.name),
|
|
parameters: buildSaAttachParameters(SA_ATTACH_DEFAULT_ALL_STATUSES),
|
|
buildSql: ({ fiscalYear, previousFiscalYear, fromDateSql, toDateSql, saAttachFilters }) =>
|
|
buildDenseQuery({
|
|
columns: SA_ATTACH_PIPELINE_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_OPPORTUNITIES_DATASET}`,
|
|
whereClauses: buildSaAttachOpportunityWhereClauses({
|
|
dataset: SA_ATTACH_OPPORTUNITIES_DATASET,
|
|
fiscalYear,
|
|
previousFiscalYear,
|
|
fromDateSql,
|
|
toDateSql,
|
|
...saAttachFilters,
|
|
}),
|
|
orderBy: `${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line ID")}`,
|
|
}),
|
|
},
|
|
"sa-attach-open-pipeline": {
|
|
description: "Single-row open pipeline metric from the SA Attach opportunity dataset with the traced tab defaults.",
|
|
entities: ["Revenue Line", "Opportunity", "Customer", "Territory"],
|
|
defaultStatuses: SA_ATTACH_DEFAULT_OPEN_STATUSES,
|
|
defaultLimit: 1,
|
|
primarySource: SOURCE_OPPORTUNITIES,
|
|
fields: SA_ATTACH_METRIC_COLUMNS.map((column) => column.name),
|
|
parameters: buildSaAttachParameters(SA_ATTACH_DEFAULT_OPEN_STATUSES),
|
|
buildSql: ({ fiscalYear, previousFiscalYear, fromDateSql, toDateSql, saAttachFilters }) =>
|
|
buildDenseQuery({
|
|
columns: SA_ATTACH_METRIC_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_OPPORTUNITIES_DATASET}`,
|
|
whereClauses: buildSaAttachOpportunityWhereClauses({
|
|
dataset: SA_ATTACH_OPPORTUNITIES_DATASET,
|
|
fiscalYear,
|
|
previousFiscalYear,
|
|
fromDateSql,
|
|
toDateSql,
|
|
...saAttachFilters,
|
|
}),
|
|
}),
|
|
},
|
|
"sa-attach-won-pipeline": {
|
|
description: "Single-row won pipeline metric from the SA Attach opportunity dataset with the traced tab defaults.",
|
|
entities: ["Revenue Line", "Opportunity", "Customer", "Territory"],
|
|
defaultStatuses: SA_ATTACH_DEFAULT_WON_STATUSES,
|
|
defaultLimit: 1,
|
|
primarySource: SOURCE_OPPORTUNITIES,
|
|
fields: SA_ATTACH_METRIC_COLUMNS.map((column) => column.name),
|
|
parameters: buildSaAttachParameters(SA_ATTACH_DEFAULT_WON_STATUSES),
|
|
buildSql: ({ fiscalYear, previousFiscalYear, fromDateSql, toDateSql, saAttachFilters }) =>
|
|
buildDenseQuery({
|
|
columns: SA_ATTACH_METRIC_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_OPPORTUNITIES_DATASET}`,
|
|
whereClauses: buildSaAttachOpportunityWhereClauses({
|
|
dataset: SA_ATTACH_OPPORTUNITIES_DATASET,
|
|
fiscalYear,
|
|
previousFiscalYear,
|
|
fromDateSql,
|
|
toDateSql,
|
|
...saAttachFilters,
|
|
}),
|
|
}),
|
|
},
|
|
"sa-attach-involvement-distribution": {
|
|
description:
|
|
"Aggregated SA Attach distribution of pipeline by involvement bucket and revenue-line probability.",
|
|
entities: ["Revenue Line", "Opportunity", "Presales Involvement"],
|
|
defaultStatuses: SA_ATTACH_DEFAULT_OPEN_STATUSES,
|
|
defaultLimit: 100,
|
|
primarySource: SOURCE_OPPORTUNITIES,
|
|
fields: SA_ATTACH_INVOLVEMENT_DISTRIBUTION_COLUMNS.map((column) => column.name),
|
|
parameters: buildSaAttachParameters(SA_ATTACH_DEFAULT_OPEN_STATUSES),
|
|
buildSql: ({ fiscalYear, previousFiscalYear, fromDateSql, toDateSql, saAttachFilters }) =>
|
|
buildDenseQuery({
|
|
columns: SA_ATTACH_INVOLVEMENT_DISTRIBUTION_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_OPPORTUNITIES_DATASET}`,
|
|
whereClauses: buildSaAttachOpportunityWhereClauses({
|
|
dataset: SA_ATTACH_OPPORTUNITIES_DATASET,
|
|
fiscalYear,
|
|
previousFiscalYear,
|
|
fromDateSql,
|
|
toDateSql,
|
|
...saAttachFilters,
|
|
}),
|
|
groupBy: `${buildSaAttachInvolvementBucketExpr()}, ${xsaColumn(
|
|
SA_ATTACH_OPPORTUNITIES_DATASET,
|
|
"Revenue Line Probability"
|
|
)}`,
|
|
orderBy: "c0, c1",
|
|
}),
|
|
},
|
|
"sa-attach-hours-by-opportunity": {
|
|
description: "Row-level SA Attach hours extract from Knowledge DV - Hours - V2. Defaults to org-wide and supports --scope my-team for current-session team filtering.",
|
|
entities: ["Presales Involvement", "Opportunity", "SR Team"],
|
|
defaultLimit: 5000,
|
|
needsFiscalYear: false,
|
|
primarySource: SOURCE_HOURS,
|
|
fields: SA_ATTACH_HOURS_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ scope = SCOPE_ORG_WIDE } = {}) =>
|
|
buildDenseQuery({
|
|
columns: SA_ATTACH_HOURS_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_HOURS_DATASET}`,
|
|
whereClauses: buildXsaScopePredicates(SA_ATTACH_HOURS_DATASET, scope),
|
|
orderBy: `${xsaColumn(SA_ATTACH_HOURS_DATASET, "Opportunity ID")}`,
|
|
}),
|
|
},
|
|
"sa-attach-presales-coverage": {
|
|
description:
|
|
"Row-level SA Attach presales coverage extract from Knowledge DV - Presales Involvement Aux - V1. Defaults to org-wide and supports --scope my-team for current-session team filtering.",
|
|
entities: ["Presales Involvement", "Opportunity", "SR Team"],
|
|
defaultLimit: 5000,
|
|
needsFiscalYear: false,
|
|
primarySource: SOURCE_PRESALES_AUX,
|
|
fields: SA_ATTACH_PRESALES_AUX_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ scope = SCOPE_ORG_WIDE } = {}) =>
|
|
buildDenseQuery({
|
|
columns: SA_ATTACH_PRESALES_AUX_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_PRESALES_AUX_DATASET}`,
|
|
whereClauses: buildXsaScopePredicates(SA_ATTACH_PRESALES_AUX_DATASET, scope),
|
|
orderBy: `${xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "Opportunity ID")}`,
|
|
}),
|
|
},
|
|
"won-and-open-by-se-opty": {
|
|
description: "One row per opportunity and SE Team Level 2 member contribution.",
|
|
entities: ["Opportunity", "Customer", "Effort", "SR Team", "Service Request", "Security"],
|
|
defaultStatuses: ["Open", "Won", "Won Pending"],
|
|
defaultLimit: 5000,
|
|
primarySource: SOURCE_DV_SE_TEAM,
|
|
fields: WON_AND_OPEN_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ fiscalYear, statuses }) =>
|
|
buildDenseQuery({
|
|
columns: WON_AND_OPEN_COLUMNS,
|
|
fromClause: 'FROM "DV - SE Team"',
|
|
whereClauses: [
|
|
'"Security"."Entry Point" = VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM")',
|
|
`"Time (Close Date Opportunity)"."Opty Close Fiscal Year" = ${quoteLiteral(fiscalYear)}`,
|
|
`"Opportunity"."Status" IN (${toSqlList(statuses)})`,
|
|
`"SR Team"."SE Team Active Flag" = 'Y'`,
|
|
`"SR Team"."SE Team Manager Flag" = 'N'`,
|
|
`"Service Request"."SR Source" = 'Opportunity'`,
|
|
`("Effort"."# Opportunity Hours" / (SUM("Effort"."# Opportunity Hours" BY "Opportunity"."Opportunity ID"))) > 0`,
|
|
],
|
|
orderBy: '"Opportunity"."Opportunity ID", "SR Team"."SE Team Level 2 Email Address"',
|
|
}),
|
|
},
|
|
"opportunities-close-window": {
|
|
description:
|
|
"One row per opportunity with close date constrained to a direct calendar-date window. It uses Knowledge DV - Opportunities - V2 as the primary org-wide commercial source. DV - SE Team is only eligible as fallback for explicit current-team reads.",
|
|
entities: ["Opportunity", "Customer", "Security"],
|
|
defaultLimit: 5000,
|
|
defaultStatuses: SA_ATTACH_DEFAULT_ALL_STATUSES,
|
|
needsFiscalYear: false,
|
|
primarySource: SOURCE_OPPORTUNITIES,
|
|
fallbackSource: SOURCE_DV_SE_TEAM,
|
|
fallbackScope: "my-team-only",
|
|
parameters: [
|
|
{
|
|
name: "fromDate",
|
|
cli: "--from-date",
|
|
required: false,
|
|
default: "today",
|
|
description: "Start of the close-date window. Accepts YYYY-MM-DD, today, +7d, -3d, or today+7d.",
|
|
},
|
|
{
|
|
name: "toDate",
|
|
cli: "--to-date",
|
|
required: false,
|
|
default: "+7d",
|
|
description: "End of the close-date window. Accepts YYYY-MM-DD, today, +7d, -3d, or today+7d.",
|
|
},
|
|
{
|
|
name: "optyStatus",
|
|
cli: "--opty-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated opportunity statuses such as Open,Won,Closed.",
|
|
},
|
|
],
|
|
fields: OPPORTUNITIES_CLOSE_WINDOW_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ fromDateSql, toDateSql, statuses, cluster = null, ownerEmail = null, ownerManagerEmail = null }) =>
|
|
buildSelectQuery({
|
|
columns: PRIMARY_OPPORTUNITY_WINDOW_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_OPPORTUNITIES_DATASET}`,
|
|
whereClauses: buildPrimaryOpportunityWhereClauses({
|
|
fromDateSql,
|
|
toDateSql,
|
|
statuses,
|
|
cluster,
|
|
ownerEmail,
|
|
ownerManagerEmail,
|
|
}),
|
|
groupBy: [
|
|
xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity ID"),
|
|
xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Name"),
|
|
xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Customer Name (Original)"),
|
|
xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Status"),
|
|
xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Date"),
|
|
].join(", "),
|
|
orderBy: "closeDate, opportunityId",
|
|
}),
|
|
buildFallbackSql: ({ fromDateSql, toDateSql, statuses }) =>
|
|
buildDenseQuery({
|
|
distinct: true,
|
|
columns: OPPORTUNITIES_CLOSE_WINDOW_COLUMNS,
|
|
fromClause: 'FROM "DV - SE Team"',
|
|
whereClauses: [
|
|
'"Security"."Entry Point" = VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM")',
|
|
`"Opportunity"."Close Date" >= ${fromDateSql}`,
|
|
`"Opportunity"."Close Date" <= ${toDateSql}`,
|
|
...(statuses.length > 0 ? [`"Opportunity"."Status" IN (${toSqlList(statuses)})`] : []),
|
|
],
|
|
orderBy: "c4, c0",
|
|
}),
|
|
},
|
|
"srs-by-resource-email": {
|
|
description:
|
|
"One row per SR for a resource email. By default it matches SR Team Level 1..4 member emails, and only uses Lead Email when explicitly requested.",
|
|
entities: ["Service Request", "Opportunity", "Customer", "SR Team", "Security"],
|
|
defaultLimit: 5000,
|
|
defaultStatuses: [],
|
|
needsFiscalYear: false,
|
|
primarySource: SOURCE_DV_SE_TEAM,
|
|
parameters: [
|
|
{
|
|
name: "resourceEmail",
|
|
cli: "--resource-email",
|
|
required: true,
|
|
description: "Oracle email of the resource to match against SR Team Level 1..4 by default.",
|
|
},
|
|
{
|
|
name: "matchRole",
|
|
cli: "--match-role",
|
|
required: false,
|
|
default: "team",
|
|
description: "Use team for SR Team Level 1..4 membership, or lead to match Service Request Lead Email explicitly.",
|
|
},
|
|
{
|
|
name: "srStatus",
|
|
cli: "--sr-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated SR statuses such as Delivery,Closed.",
|
|
},
|
|
],
|
|
fields: SRS_BY_RESOURCE_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ resourceEmailSql, matchRole, srStatuses }) =>
|
|
buildDenseQuery({
|
|
distinct: true,
|
|
columns: SRS_BY_RESOURCE_COLUMNS,
|
|
fromClause: 'FROM "DV - SE Team"',
|
|
whereClauses: [
|
|
'"Security"."Entry Point" = VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM")',
|
|
buildSrResourcePredicate(resourceEmailSql, matchRole),
|
|
...(srStatuses.length > 0 ? [`"Service Request"."Status" IN (${toSqlList(srStatuses)})`] : []),
|
|
],
|
|
orderBy: "c0",
|
|
}),
|
|
},
|
|
"data-quality-session-context": {
|
|
description: "Returns the DV session entry point plus fiscal period variables.",
|
|
entities: ["Security", "Session Variables"],
|
|
defaultLimit: 1,
|
|
needsFiscalYear: false,
|
|
primarySource: SOURCE_DV_SE_TEAM,
|
|
fields: DATA_QUALITY_CONTEXT_COLUMNS.map((column) => column.name),
|
|
buildSql: () =>
|
|
buildDenseQuery({
|
|
columns: DATA_QUALITY_CONTEXT_COLUMNS,
|
|
fromClause: 'FROM "DV - SE Team"',
|
|
whereClauses: ['"Security"."Entry Point" = VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM")'],
|
|
orderBy: '"Security"."Entry Point"',
|
|
}),
|
|
},
|
|
"effort-task-type-summary": {
|
|
description: "Aggregated effort hours by task type for a fiscal year.",
|
|
entities: ["Effort", "Effort Details", "SR Team", "Security"],
|
|
defaultLimit: 100,
|
|
primarySource: SOURCE_DV_SE_TEAM,
|
|
fields: EFFORT_TASK_TYPE_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ fiscalYear }) =>
|
|
buildDenseQuery({
|
|
columns: EFFORT_TASK_TYPE_COLUMNS,
|
|
fromClause: 'FROM "DV - SE Team"',
|
|
whereClauses: [
|
|
'"Security"."Entry Point" = VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM")',
|
|
`"Time (Effort)"."Effort Fiscal Year" = ${quoteLiteral(fiscalYear)}`,
|
|
`"SR Team"."SE Team Active Flag" = 'Y'`,
|
|
`"SR Team"."SE Team Manager Flag" = 'N'`,
|
|
],
|
|
orderBy: '"Effort"."# Hours (Excl. Time Off)" DESC, "Effort Details"."Task Type"',
|
|
}),
|
|
},
|
|
"effort-by-se-team": {
|
|
description: "Aggregated effort hours by SE Team Level 2 member for a fiscal year.",
|
|
entities: ["Effort", "SR Team", "Security"],
|
|
defaultLimit: 100,
|
|
primarySource: SOURCE_DV_SE_TEAM,
|
|
fields: EFFORT_BY_TEAM_COLUMNS.map((column) => column.name),
|
|
buildSql: ({ fiscalYear }) =>
|
|
buildDenseQuery({
|
|
columns: EFFORT_BY_TEAM_COLUMNS,
|
|
fromClause: 'FROM "DV - SE Team"',
|
|
whereClauses: [
|
|
'"Security"."Entry Point" = VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM")',
|
|
`"Time (Effort)"."Effort Fiscal Year" = ${quoteLiteral(fiscalYear)}`,
|
|
`"SR Team"."SE Team Active Flag" = 'Y'`,
|
|
`"SR Team"."SE Team Manager Flag" = 'N'`,
|
|
],
|
|
orderBy: '"Effort"."# Hours (Excl. Time Off)" DESC, "SR Team"."SE Team Level 2 Email Address"',
|
|
}),
|
|
},
|
|
};
|
|
|
|
function closeWindowClusterParameter() {
|
|
return {
|
|
name: "cluster",
|
|
cli: "--cluster",
|
|
required: false,
|
|
description:
|
|
"Optional explicit commercial opportunity Cluster filter. When omitted, the existing unfiltered geography behavior is preserved.",
|
|
};
|
|
}
|
|
|
|
function saAttachClusterParameter() {
|
|
return {
|
|
name: "cluster",
|
|
cli: "--cluster",
|
|
required: false,
|
|
default: SA_ATTACH_DEFAULT_CLUSTER,
|
|
description: "Optional SA Attach Cluster filter. Defaults to Brazil when omitted.",
|
|
};
|
|
}
|
|
|
|
function workloadClusterParameter() {
|
|
return {
|
|
name: "cluster",
|
|
cli: "--cluster",
|
|
required: false,
|
|
default: SA_ATTACH_DEFAULT_CLUSTER,
|
|
description: "Optional workload commercial Cluster filter. Defaults to Brazil when omitted.",
|
|
};
|
|
}
|
|
|
|
function closeWindowDateParameters() {
|
|
return [
|
|
{
|
|
name: "fromDate",
|
|
cli: "--from-date",
|
|
required: false,
|
|
default: "today",
|
|
description: "Start of the close-date window. Accepts YYYY-MM-DD, today, +7d, -3d, or today+7d.",
|
|
},
|
|
{
|
|
name: "toDate",
|
|
cli: "--to-date",
|
|
required: false,
|
|
default: "+7d",
|
|
description: "End of the close-date window. Accepts YYYY-MM-DD, today, +7d, -3d, or today+7d.",
|
|
},
|
|
{
|
|
name: "date",
|
|
cli: "--date",
|
|
required: false,
|
|
description: "Exact close date. Shortcut for a one-day window.",
|
|
},
|
|
{
|
|
name: "month",
|
|
cli: "--month",
|
|
required: false,
|
|
description: "Calendar month in YYYY-MM format. Shortcut for a full-month window.",
|
|
},
|
|
{
|
|
name: "quarter",
|
|
cli: "--quarter",
|
|
required: false,
|
|
description: "Fiscal quarter in FY26-Q4 or Q4 FY26 format. Shortcut for a full fiscal-quarter window.",
|
|
},
|
|
{
|
|
name: "week",
|
|
cli: "--week",
|
|
required: false,
|
|
description: "Annual week shortcut anchored on June 1. Accepts 1..53, w1..w53, or week 1..week 53.",
|
|
},
|
|
{
|
|
name: "weekYear",
|
|
cli: "--week-year",
|
|
required: false,
|
|
default: "current June-cycle start year",
|
|
description: "Cycle start year used with --week. For example, 2025 means the 2025-06-01 through 2026-05-31 cycle.",
|
|
},
|
|
];
|
|
}
|
|
|
|
function limitParameter(description = "Optional row limit for the extract.") {
|
|
return {
|
|
name: "limit",
|
|
cli: "--limit",
|
|
required: false,
|
|
default: "5000",
|
|
description,
|
|
};
|
|
}
|
|
|
|
const INTENT_CATALOG = {
|
|
"opportunities-default": {
|
|
description: "Default opportunity path using the traced commercial pipeline defaults on Knowledge DV - Opportunities - V2.",
|
|
view: "sa-attach-pipeline-details",
|
|
parameters: [
|
|
saAttachClusterParameter(),
|
|
{
|
|
name: "limit",
|
|
cli: "--limit",
|
|
required: false,
|
|
default: "5000",
|
|
description: "Optional row limit for the default commercial pipeline extract.",
|
|
},
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-default --limit 200`,
|
|
],
|
|
},
|
|
"workloads-by-owner-current": {
|
|
description: "Current-state workload view for one explicit territory owner, defaulting to the current fiscal quarter and workload solution family.",
|
|
view: "sa-attach-pipeline-details",
|
|
parameters: [
|
|
{
|
|
name: "ownerEmail",
|
|
cli: "--owner-email",
|
|
required: true,
|
|
description: "Territory-owner email to match on the direct owner of the workload row.",
|
|
},
|
|
{
|
|
name: "quarter",
|
|
cli: "--quarter",
|
|
required: false,
|
|
default: "current-quarter",
|
|
description: "Fiscal quarter in FY26-Q4 or Q4 FY26 format. Defaults to current-quarter.",
|
|
},
|
|
{
|
|
name: "forecastType",
|
|
cli: "--forecast-type",
|
|
required: false,
|
|
default: "all forecast types",
|
|
description: "Optional forecast-type filter such as Forecast or Upside.",
|
|
},
|
|
saAttachClusterParameter(),
|
|
{
|
|
name: "limit",
|
|
cli: "--limit",
|
|
required: false,
|
|
default: "5000",
|
|
description: "Optional row limit for the workload extract.",
|
|
},
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent workloads-by-owner-current --owner-email territory.owner@oracle.com`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent workloads-by-owner-current --owner-email territory.owner@oracle.com --forecast-type Forecast`,
|
|
],
|
|
},
|
|
"workloads-by-owner-manager-current": {
|
|
description: "Current-state workload view for owners under one explicit manager-of-owner, defaulting to the current fiscal quarter and workload solution family.",
|
|
view: "sa-attach-pipeline-details",
|
|
parameters: [
|
|
{
|
|
name: "ownerManagerEmail",
|
|
cli: "--owner-manager-email",
|
|
required: true,
|
|
description: "Upper territory-owner manager email to match in the hierarchy.",
|
|
},
|
|
{
|
|
name: "quarter",
|
|
cli: "--quarter",
|
|
required: false,
|
|
default: "current-quarter",
|
|
description: "Fiscal quarter in FY26-Q4 or Q4 FY26 format. Defaults to current-quarter.",
|
|
},
|
|
{
|
|
name: "forecastType",
|
|
cli: "--forecast-type",
|
|
required: false,
|
|
default: "all forecast types",
|
|
description: "Optional forecast-type filter such as Forecast or Upside.",
|
|
},
|
|
saAttachClusterParameter(),
|
|
{
|
|
name: "limit",
|
|
cli: "--limit",
|
|
required: false,
|
|
default: "5000",
|
|
description: "Optional row limit for the workload extract.",
|
|
},
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent workloads-by-owner-manager-current --owner-manager-email territory.manager@oracle.com`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent workloads-by-owner-manager-current --owner-manager-email territory.manager@oracle.com --forecast-type Forecast`,
|
|
],
|
|
},
|
|
"workloads-by-owner-close-window": {
|
|
description: "List org-wide workload revenue lines in a close-date window for one explicit territory owner.",
|
|
view: "sa-attach-pipeline-details",
|
|
parameters: [
|
|
{
|
|
name: "ownerEmail",
|
|
cli: "--owner-email",
|
|
required: true,
|
|
description: "Territory-owner email to match on the direct owner of the workload row.",
|
|
},
|
|
...closeWindowDateParameters(),
|
|
{
|
|
name: "forecastType",
|
|
cli: "--forecast-type",
|
|
required: false,
|
|
default: "all forecast types",
|
|
description: "Optional forecast-type filter such as Forecast, Upside, or Won.",
|
|
},
|
|
workloadClusterParameter(),
|
|
limitParameter("Optional row limit for the workload extract."),
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent workloads-by-owner-close-window --owner-email territory.owner@oracle.com --month 2026-05`,
|
|
],
|
|
},
|
|
"workloads-by-owner-manager-close-window": {
|
|
description: "List org-wide workload revenue lines in a close-date window for territory owners under one explicit owner manager.",
|
|
view: "sa-attach-pipeline-details",
|
|
parameters: [
|
|
{
|
|
name: "ownerManagerEmail",
|
|
cli: "--owner-manager-email",
|
|
required: true,
|
|
description: "Upper territory-owner manager email to match in the workload owner hierarchy.",
|
|
},
|
|
...closeWindowDateParameters(),
|
|
{
|
|
name: "forecastType",
|
|
cli: "--forecast-type",
|
|
required: false,
|
|
default: "all forecast types",
|
|
description: "Optional forecast-type filter such as Forecast, Upside, or Won.",
|
|
},
|
|
workloadClusterParameter(),
|
|
limitParameter("Optional row limit for the workload extract."),
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent workloads-by-owner-manager-close-window --owner-manager-email territory.manager@oracle.com --week w43`,
|
|
],
|
|
},
|
|
"workloads-by-resource-close-window": {
|
|
description: "List org-wide workload revenue lines in a close-date window for opportunities where one explicit resource appears in Presales Involvement Aux coverage.",
|
|
view: "sa-attach-pipeline-details",
|
|
parameters: [
|
|
{
|
|
name: "resource",
|
|
cli: "--resource",
|
|
required: true,
|
|
description: "Oracle user identifier. Accepts resource.member or resource.member@oracle.com.",
|
|
},
|
|
...closeWindowDateParameters(),
|
|
{
|
|
name: "forecastType",
|
|
cli: "--forecast-type",
|
|
required: false,
|
|
default: "all forecast types",
|
|
description: "Optional forecast-type filter such as Forecast, Upside, or Won.",
|
|
},
|
|
workloadClusterParameter(),
|
|
limitParameter("Optional row limit for the org-wide workload revenue-line extract before coverage intersection."),
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent workloads-by-resource-close-window --resource resource.member --week w43`,
|
|
],
|
|
},
|
|
"workloads-by-manager-resource-close-window": {
|
|
description:
|
|
"List org-wide workload revenue lines closing inside a date window, keeping only opportunities that have allocated presales resources under one or more explicit managers from Knowledge DV - Presales Involvement Aux - V1. This default intent is involvement/allocation only and does not fetch SR details from DV - SE Team.",
|
|
view: "sa-attach-pipeline-details",
|
|
parameters: [
|
|
{
|
|
name: "managerEmailList",
|
|
cli: "--manager-email-list",
|
|
required: true,
|
|
description:
|
|
"Comma-separated manager emails used to anchor Presales Involvement Aux coverage. Accepts short ids or full @oracle.com emails.",
|
|
},
|
|
{
|
|
name: "managerEmail",
|
|
cli: "--manager-email",
|
|
required: false,
|
|
description:
|
|
"Optional single manager email. It is merged with --manager-email-list when both are present.",
|
|
},
|
|
{
|
|
name: "fromDate",
|
|
cli: "--from-date",
|
|
required: false,
|
|
default: "today",
|
|
description: "Start of the revenue-line close-date window. Accepts YYYY-MM-DD, today, +7d, -3d, or today+7d.",
|
|
},
|
|
{
|
|
name: "toDate",
|
|
cli: "--to-date",
|
|
required: false,
|
|
default: "+7d",
|
|
description: "End of the revenue-line close-date window. Accepts YYYY-MM-DD, today, +7d, -3d, or today+7d.",
|
|
},
|
|
{
|
|
name: "date",
|
|
cli: "--date",
|
|
required: false,
|
|
description: "Exact revenue-line close date. Shortcut for a one-day window.",
|
|
},
|
|
{
|
|
name: "month",
|
|
cli: "--month",
|
|
required: false,
|
|
description: "Calendar month in YYYY-MM format. Shortcut for a full-month revenue-line window.",
|
|
},
|
|
{
|
|
name: "quarter",
|
|
cli: "--quarter",
|
|
required: false,
|
|
description: "Fiscal quarter in FY26-Q4 or Q4 FY26 format. Shortcut for a full fiscal-quarter revenue-line window.",
|
|
},
|
|
{
|
|
name: "week",
|
|
cli: "--week",
|
|
required: false,
|
|
description: "Annual week shortcut anchored on June 1. Accepts 1..53, w1..w53, or week 1..week 53.",
|
|
},
|
|
{
|
|
name: "weekYear",
|
|
cli: "--week-year",
|
|
required: false,
|
|
default: "current June-cycle start year",
|
|
description: "Cycle start year used with --week. For example, 2025 means the 2025-06-01 through 2026-05-31 cycle.",
|
|
},
|
|
workloadClusterParameter(),
|
|
{
|
|
name: "limit",
|
|
cli: "--limit",
|
|
required: false,
|
|
default: "5000",
|
|
description: "Optional row limit for the org-wide workload revenue-line extract before coverage intersection.",
|
|
},
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent workloads-by-manager-resource-close-window --manager-email-list resource.manager.a@oracle.com,resource.manager.b@oracle.com --from-date today --to-date +7d`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent workloads-by-manager-resource-close-window --manager-email resource.manager@oracle.com --week w2 --week-year 2026`,
|
|
],
|
|
},
|
|
"opportunities-close-date": {
|
|
description: "List opportunities closing on one exact date.",
|
|
view: "opportunities-close-window",
|
|
parameters: [
|
|
{
|
|
name: "date",
|
|
cli: "--date",
|
|
required: true,
|
|
description: "Exact close date. Accepts YYYY-MM-DD, today, +7d, -3d, or today+7d.",
|
|
},
|
|
{
|
|
name: "optyStatus",
|
|
cli: "--opty-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated opportunity statuses such as Open,Won.",
|
|
},
|
|
closeWindowClusterParameter(),
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-close-date --date 2026-05-02`,
|
|
],
|
|
},
|
|
"opportunities-close-month": {
|
|
description: "List opportunities closing inside one calendar month.",
|
|
view: "opportunities-close-window",
|
|
parameters: [
|
|
{
|
|
name: "month",
|
|
cli: "--month",
|
|
required: true,
|
|
description: "Calendar month in YYYY-MM format, for example 2026-04.",
|
|
},
|
|
{
|
|
name: "optyStatus",
|
|
cli: "--opty-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated opportunity statuses such as Open,Won.",
|
|
},
|
|
closeWindowClusterParameter(),
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-close-month --month 2026-04`,
|
|
],
|
|
},
|
|
"opportunities-close-quarter": {
|
|
description: "List opportunities closing inside one fiscal quarter.",
|
|
view: "opportunities-close-window",
|
|
parameters: [
|
|
{
|
|
name: "quarter",
|
|
cli: "--quarter",
|
|
required: true,
|
|
description: "Fiscal quarter in FY26-Q4 or Q4 FY26 format. Also accepts current-quarter.",
|
|
},
|
|
{
|
|
name: "optyStatus",
|
|
cli: "--opty-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated opportunity statuses such as Open,Won.",
|
|
},
|
|
closeWindowClusterParameter(),
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-close-quarter --quarter FY26-Q4`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-close-quarter --quarter current-quarter`,
|
|
],
|
|
},
|
|
"opportunities-by-owner-close-window": {
|
|
description: "List org-wide commercial opportunities/workloads in a close-date window for one explicit territory owner.",
|
|
view: "opportunities-close-window",
|
|
parameters: [
|
|
{
|
|
name: "ownerEmail",
|
|
cli: "--owner-email",
|
|
required: true,
|
|
description: "Territory-owner email to match on the direct owner of the opportunity row.",
|
|
},
|
|
...closeWindowDateParameters(),
|
|
{
|
|
name: "optyStatus",
|
|
cli: "--opty-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated opportunity statuses such as Open,Won,Closed.",
|
|
},
|
|
closeWindowClusterParameter(),
|
|
limitParameter("Optional row limit for the opportunity window."),
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-by-owner-close-window --owner-email territory.owner@oracle.com --week w43`,
|
|
],
|
|
},
|
|
"opportunities-by-owner-manager-close-window": {
|
|
description: "List org-wide commercial opportunities/workloads in a close-date window for territory owners under one explicit owner manager.",
|
|
view: "opportunities-close-window",
|
|
parameters: [
|
|
{
|
|
name: "ownerManagerEmail",
|
|
cli: "--owner-manager-email",
|
|
required: true,
|
|
description: "Upper territory-owner manager email to match in the opportunity owner hierarchy.",
|
|
},
|
|
...closeWindowDateParameters(),
|
|
{
|
|
name: "optyStatus",
|
|
cli: "--opty-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated opportunity statuses such as Open,Won,Closed.",
|
|
},
|
|
closeWindowClusterParameter(),
|
|
limitParameter("Optional row limit for the opportunity window."),
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-by-owner-manager-close-window --owner-manager-email territory.manager@oracle.com --month 2026-05`,
|
|
],
|
|
},
|
|
"opportunities-current-team-close-window": {
|
|
description:
|
|
"List commercial opportunities/workloads in a close-date window and enrich them with current-team presales/SR coverage for the executing user when present. Org-wide opportunity rows are preserved even when no current-team resource matches.",
|
|
view: "opportunities-close-window",
|
|
parameters: [
|
|
...closeWindowDateParameters(),
|
|
{
|
|
name: "optyStatus",
|
|
cli: "--opty-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated opportunity statuses such as Open,Won,Closed.",
|
|
},
|
|
closeWindowClusterParameter(),
|
|
{
|
|
name: "limit",
|
|
cli: "--limit",
|
|
required: false,
|
|
default: "5000",
|
|
description: "Optional row limit for the initial opportunity window before current-team coverage enrichment.",
|
|
},
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-current-team-close-window --from-date today --to-date +4d`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-current-team-close-window --date 2026-05-02`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-current-team-close-window --quarter current-quarter`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-current-team-close-window --week w2 --week-year 2026`,
|
|
],
|
|
},
|
|
"opportunities-by-resource-close-window": {
|
|
description:
|
|
"List commercial opportunities/workloads in a close-date window and enrich them with presales coverage for one explicit Oracle resource email when present. Org-wide opportunity rows are preserved even when no resource match exists.",
|
|
view: "opportunities-close-window",
|
|
parameters: [
|
|
{
|
|
name: "resource",
|
|
cli: "--resource",
|
|
required: true,
|
|
description: "Oracle user identifier. Accepts resource.member or resource.member@oracle.com.",
|
|
},
|
|
...closeWindowDateParameters(),
|
|
{
|
|
name: "optyStatus",
|
|
cli: "--opty-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated opportunity statuses such as Open,Won,Closed.",
|
|
},
|
|
closeWindowClusterParameter(),
|
|
{
|
|
name: "limit",
|
|
cli: "--limit",
|
|
required: false,
|
|
default: "5000",
|
|
description: "Optional row limit for the initial opportunity window before coverage enrichment.",
|
|
},
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-by-resource-close-window --resource resource.member --month 2026-04`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-by-resource-close-window --resource resource.member --quarter current-quarter`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-by-resource-close-window --resource resource.member --week w2 --week-year 2026`,
|
|
],
|
|
},
|
|
"opportunities-by-manager-close-window": {
|
|
description:
|
|
"List commercial opportunities/workloads in a close-date window and enrich them with coverage rows anchored to one explicit manager email when present. Org-wide opportunity rows are preserved even when no manager-team match exists.",
|
|
view: "opportunities-close-window",
|
|
parameters: [
|
|
{
|
|
name: "managerEmail",
|
|
cli: "--manager-email",
|
|
required: true,
|
|
description: "Manager email to match in the coverage hierarchy. Accepts resource.manager or resource.manager@oracle.com.",
|
|
},
|
|
...closeWindowDateParameters(),
|
|
{
|
|
name: "optyStatus",
|
|
cli: "--opty-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated opportunity statuses such as Open,Won,Closed.",
|
|
},
|
|
closeWindowClusterParameter(),
|
|
{
|
|
name: "limit",
|
|
cli: "--limit",
|
|
required: false,
|
|
default: "5000",
|
|
description: "Optional row limit for the initial opportunity window before coverage enrichment.",
|
|
},
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-by-manager-close-window --manager-email resource.manager@oracle.com --month 2026-04`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-by-manager-close-window --manager-email resource.manager@oracle.com --quarter current-quarter`,
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent opportunities-by-manager-close-window --manager-email resource.manager@oracle.com --week w2 --week-year 2026`,
|
|
],
|
|
},
|
|
"srs-by-resource": {
|
|
description: "List SRs for one Oracle resource, matching SR Team Level 1..4 by default.",
|
|
view: "srs-by-resource-email",
|
|
parameters: [
|
|
{
|
|
name: "resource",
|
|
cli: "--resource",
|
|
required: true,
|
|
description: "Oracle user identifier. Accepts resource.member or resource.member@oracle.com.",
|
|
},
|
|
{
|
|
name: "matchRole",
|
|
cli: "--match-role",
|
|
required: false,
|
|
default: "team",
|
|
description: "Use team by default. Use lead only when explicitly looking for the SR leader.",
|
|
},
|
|
{
|
|
name: "srStatus",
|
|
cli: "--sr-status",
|
|
required: false,
|
|
default: "all statuses",
|
|
description: "Optional comma-separated SR statuses such as Delivery,Closed.",
|
|
},
|
|
],
|
|
examples: [
|
|
`node "${path.resolve(process.argv[1] || "fetch-sales-intelligence.mjs")}" --intent srs-by-resource --resource resource.member`,
|
|
],
|
|
},
|
|
};
|
|
|
|
const MACRO_CATALOG = {
|
|
subjectArea: {
|
|
description: 'Primary Oracle DV subject area for this skill.',
|
|
resolve: () => '"DV - SE Team"',
|
|
},
|
|
securityEntryPointFilter: {
|
|
description: 'Team security predicate used by most direct queries.',
|
|
resolve: () => '"Security"."Entry Point" = VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM")',
|
|
},
|
|
sessionCurrentFiscalYear: {
|
|
description: 'Oracle DV session expression for the current fiscal year.',
|
|
resolve: () => "valueof(RV_CURR_FISCAL_YEAR)",
|
|
},
|
|
sessionCurrentFiscalWeek: {
|
|
description: 'Oracle DV session expression for the current fiscal week.',
|
|
resolve: () => "valueof(RV_CURR_FISCAL_WEEK)",
|
|
},
|
|
sessionPreviousFiscalWeek: {
|
|
description: 'Oracle DV session expression for the previous fiscal week.',
|
|
resolve: () => "valueof(RV_PREV_FISCAL_WEEK)",
|
|
},
|
|
saAttachOpportunitiesSubjectArea: {
|
|
description: "SA Attach opportunity XSA dataset used for pipeline and involvement views.",
|
|
resolve: () => SA_ATTACH_OPPORTUNITIES_DATASET,
|
|
},
|
|
saAttachHoursSubjectArea: {
|
|
description: "SA Attach hours XSA dataset. It is org-wide-capable; add --scope my-team or a session filter for current-team reads.",
|
|
resolve: () => SA_ATTACH_HOURS_DATASET,
|
|
},
|
|
saAttachPresalesAuxSubjectArea: {
|
|
description: "SA Attach presales-coverage XSA dataset. It is org-wide-capable; add --scope my-team or a session filter for current-team reads.",
|
|
resolve: () => SA_ATTACH_PRESALES_AUX_DATASET,
|
|
},
|
|
saAttachRevenueLinesSubjectArea: {
|
|
description: "SA Attach revenue-line XSA dataset reused by the revenue-line view.",
|
|
resolve: () => SA_ATTACH_REVENUE_LINES_DATASET,
|
|
},
|
|
saAttachInvolvementBucket: {
|
|
description: "SA Attach CASE expression that classifies my-team, other, or no presales involvement.",
|
|
resolve: () => buildSaAttachInvolvementBucketExpr(),
|
|
},
|
|
};
|
|
|
|
const PARAMETER_CATALOG = [
|
|
{
|
|
name: "catalog",
|
|
cli: "--catalog",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
default: false,
|
|
description:
|
|
"Return one JSON payload with views, intents, entities, fields, macros, parameters, and script metadata.",
|
|
},
|
|
{
|
|
name: "check-auth",
|
|
cli: "--check-auth",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["auth", "healthcheck"],
|
|
default: false,
|
|
description: "Validate the saved Oracle DV session without opening the browser.",
|
|
},
|
|
{
|
|
name: "list-views",
|
|
cli: "--list-views",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
default: false,
|
|
description: "List built-in views with fields, entities, and parameter contracts.",
|
|
},
|
|
{
|
|
name: "describe-view",
|
|
cli: "--describe-view",
|
|
kind: "string",
|
|
valueLabel: "<view>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
description: "Describe one built-in view by name.",
|
|
examples: ["--describe-view opportunity-details"],
|
|
},
|
|
{
|
|
name: "list-intents",
|
|
cli: "--list-intents",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
default: false,
|
|
description: "List recurring business-intent shortcuts.",
|
|
},
|
|
{
|
|
name: "describe-intent",
|
|
cli: "--describe-intent",
|
|
kind: "string",
|
|
valueLabel: "<intent>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
description: "Describe one intent and its parameter contract.",
|
|
examples: ["--describe-intent opportunities-close-month"],
|
|
},
|
|
{
|
|
name: "list-entities",
|
|
cli: "--list-entities",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
default: false,
|
|
description: "List logical entities and the views that expose them.",
|
|
},
|
|
{
|
|
name: "list-fields",
|
|
cli: "--list-fields",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
default: false,
|
|
description: "List the local logical-field catalog.",
|
|
},
|
|
{
|
|
name: "describe-field",
|
|
cli: "--describe-field",
|
|
kind: "string",
|
|
valueLabel: "<fieldAlias>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
description: "Describe one field alias and its Oracle expression.",
|
|
examples: ["--describe-field closeDate"],
|
|
},
|
|
{
|
|
name: "list-parameters",
|
|
cli: "--list-parameters",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
default: false,
|
|
description: "List the full CLI parameter catalog for the skill scripts.",
|
|
},
|
|
{
|
|
name: "describe-parameter",
|
|
cli: "--describe-parameter",
|
|
kind: "string",
|
|
valueLabel: "<name|--flag>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
description: "Describe one parameter by bare name or CLI flag.",
|
|
examples: ["--describe-parameter resource", "--describe-parameter --from-date"],
|
|
},
|
|
{
|
|
name: "list-scripts",
|
|
cli: "--list-scripts",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
default: false,
|
|
description: "List the runnable scripts bundled in this skill.",
|
|
},
|
|
{
|
|
name: "describe-script",
|
|
cli: "--describe-script",
|
|
kind: "string",
|
|
valueLabel: "<script>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
description: "Describe one runnable script and its supported parameters.",
|
|
examples: ["--describe-script fetch-sales-intelligence.mjs"],
|
|
},
|
|
{
|
|
name: "entity",
|
|
cli: "--entity",
|
|
kind: "string",
|
|
valueLabel: "<entity>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
onlyWith: ["--list-fields"],
|
|
description: "Filter --list-fields to one entity name.",
|
|
examples: ["--list-fields --entity Opportunity"],
|
|
},
|
|
{
|
|
name: "role",
|
|
cli: "--role",
|
|
kind: "string",
|
|
valueLabel: "<role>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
onlyWith: ["--list-fields"],
|
|
description: "Filter --list-fields to one field role such as select, filter, aggregate, or order.",
|
|
examples: ["--list-fields --role filter"],
|
|
},
|
|
{
|
|
name: "search",
|
|
cli: "--search",
|
|
kind: "string",
|
|
valueLabel: "<text>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["discovery"],
|
|
onlyWith: ["--list-fields"],
|
|
description: "Text search across field alias, entity, expression, and description.",
|
|
examples: ["--list-fields --search manager"],
|
|
},
|
|
{
|
|
name: "view",
|
|
cli: "--view",
|
|
kind: "string",
|
|
valueLabel: "<view>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution"],
|
|
conflictsWith: ["--intent", "--query", "--sql"],
|
|
description: "Execute one built-in view directly.",
|
|
},
|
|
{
|
|
name: "intent",
|
|
cli: "--intent",
|
|
kind: "string",
|
|
valueLabel: "<intent>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution"],
|
|
conflictsWith: ["--view", "--query", "--sql"],
|
|
description: "Execute one higher-level intent shortcut.",
|
|
},
|
|
{
|
|
name: "sql",
|
|
cli: "--sql",
|
|
kind: "string",
|
|
valueLabel: "\"<logical sql>\"",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution", "sql"],
|
|
conflictsWith: ["--view", "--intent", "--query"],
|
|
description: "Run custom Oracle logical SQL. Supports local placeholder expansion.",
|
|
examples: ['--sql "SELECT {{opportunityId}}, {{closeDate}} FROM {{subjectArea}} WHERE {{securityEntryPointFilter}}"'],
|
|
},
|
|
{
|
|
name: "query",
|
|
cli: "--query",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution", "query"],
|
|
conflictsWith: ["--view", "--intent", "--sql"],
|
|
description: "Enable declarative query mode. Requires --select and/or --measure.",
|
|
},
|
|
{
|
|
name: "render-sql",
|
|
cli: "--render-sql",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution", "sql", "query"],
|
|
default: false,
|
|
description: "Render the final SQL locally for --sql or --query without calling Oracle DV.",
|
|
},
|
|
{
|
|
name: "select",
|
|
cli: "--select",
|
|
kind: "csv",
|
|
valueLabel: "fieldA,fieldB",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["query"],
|
|
onlyWith: ["--query"],
|
|
description: "Row-level field aliases for declarative query mode.",
|
|
examples: ["--query --select opportunityId,opportunityName,closeDate"],
|
|
},
|
|
{
|
|
name: "measure",
|
|
cli: "--measure",
|
|
kind: "csv",
|
|
valueLabel: "aggregate:field[:alias]",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["query"],
|
|
onlyWith: ["--query"],
|
|
description:
|
|
"Aggregate outputs for declarative query mode. Format: aggregate:fieldAlias[:outputAlias].",
|
|
examples: ["--measure sum:bookingValue:totalBooking,count-distinct:opportunityId:optyCount"],
|
|
},
|
|
{
|
|
name: "where",
|
|
cli: "--where",
|
|
kind: "string",
|
|
valueLabel: "\"<predicate>\"",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["query"],
|
|
onlyWith: ["--query"],
|
|
description: "Row filter clause for declarative query mode. Field aliases are resolved automatically.",
|
|
},
|
|
{
|
|
name: "group-by",
|
|
cli: "--group-by",
|
|
kind: "csv",
|
|
valueLabel: "fieldA,fieldB",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["query"],
|
|
onlyWith: ["--query"],
|
|
description: "Explicit group-by outputs or field aliases for declarative query mode.",
|
|
},
|
|
{
|
|
name: "having",
|
|
cli: "--having",
|
|
kind: "string",
|
|
valueLabel: "\"<predicate>\"",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["query"],
|
|
onlyWith: ["--query"],
|
|
description: "Post-aggregation filter clause for declarative query mode.",
|
|
},
|
|
{
|
|
name: "order-by",
|
|
cli: "--order-by",
|
|
kind: "csv",
|
|
valueLabel: "field:asc|desc",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["query"],
|
|
onlyWith: ["--query"],
|
|
description: "Output ordering for declarative query mode.",
|
|
examples: ["--order-by totalBooking:desc,closeDate:asc"],
|
|
},
|
|
{
|
|
name: "distinct",
|
|
cli: "--distinct",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["query"],
|
|
onlyWith: ["--query"],
|
|
default: false,
|
|
description: "Emit SELECT DISTINCT in declarative query mode.",
|
|
},
|
|
{
|
|
name: "date",
|
|
cli: "--date",
|
|
kind: "string",
|
|
valueLabel: "YYYY-MM-DD",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["date-window"],
|
|
description: "Exact close date for close-window views and intents.",
|
|
examples: ["--date 2026-05-02"],
|
|
},
|
|
{
|
|
name: "month",
|
|
cli: "--month",
|
|
kind: "string",
|
|
valueLabel: "YYYY-MM|current-month",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["date-window"],
|
|
description: "Calendar month shortcut for close-window views and intents. Also accepts current-month.",
|
|
examples: ["--month 2026-04", "--month current-month"],
|
|
},
|
|
{
|
|
name: "quarter",
|
|
cli: "--quarter",
|
|
kind: "string",
|
|
valueLabel: "FY26-Q4|Q4 FY26|current-quarter",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["date-window"],
|
|
description: "Fiscal quarter shortcut for close-window views and intents.",
|
|
examples: ["--quarter FY26-Q4", "--quarter current-quarter"],
|
|
},
|
|
{
|
|
name: "week",
|
|
cli: "--week",
|
|
kind: "string",
|
|
valueLabel: "1|w1|week 1|current-week",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["date-window"],
|
|
description:
|
|
"Annual week shortcut anchored on June 1. Week numbers continue through December, wrap across January, and end on the last days of May. Also accepts current-week.",
|
|
examples: ["--week w2", "--week 43", "--week 53", "--week current-week"],
|
|
},
|
|
{
|
|
name: "week-year",
|
|
cli: "--week-year",
|
|
kind: "number",
|
|
valueLabel: "YYYY",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["date-window"],
|
|
default: new Date().getMonth() >= 5 ? new Date().getFullYear() : new Date().getFullYear() - 1,
|
|
description: "Cycle start year used with --week. Defaults to the current June-cycle start year.",
|
|
examples: ["--week-year 2025"],
|
|
},
|
|
{
|
|
name: "from-date",
|
|
cli: "--from-date",
|
|
kind: "string",
|
|
valueLabel: "YYYY-MM-DD|today|+7d",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["date-window"],
|
|
description: "Start of a close-date window.",
|
|
},
|
|
{
|
|
name: "to-date",
|
|
cli: "--to-date",
|
|
kind: "string",
|
|
valueLabel: "YYYY-MM-DD|today|+7d",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["date-window"],
|
|
description: "End of a close-date window.",
|
|
},
|
|
{
|
|
name: "opty-status",
|
|
cli: "--opty-status",
|
|
kind: "csv",
|
|
valueLabel: "Open,Won,Closed",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["view", "intent", "date-window"],
|
|
description: "Optional opportunity status filter.",
|
|
},
|
|
{
|
|
name: "resource-email",
|
|
cli: "--resource-email",
|
|
kind: "string",
|
|
valueLabel: "<user@oracle.com>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["resource"],
|
|
description: "Explicit Oracle email for SR team matching.",
|
|
},
|
|
{
|
|
name: "resource",
|
|
cli: "--resource",
|
|
kind: "string",
|
|
valueLabel: "<uid|email>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["resource"],
|
|
description: "Short Oracle user id or full email. Normalizes uid to @oracle.com.",
|
|
examples: ["--resource resource.member"],
|
|
},
|
|
{
|
|
name: "manager-email",
|
|
cli: "--manager-email",
|
|
kind: "string",
|
|
valueLabel: "<user@oracle.com>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["resource"],
|
|
description: "Explicit manager email for hierarchy-scoped SA Attach opportunity lookups.",
|
|
examples: ["--manager-email resource.manager@oracle.com"],
|
|
},
|
|
{
|
|
name: "manager-email-list",
|
|
cli: "--manager-email-list",
|
|
kind: "csv",
|
|
valueLabel: "emailA,emailB",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["resource"],
|
|
description:
|
|
"Comma-separated manager emails for org-wide workload coverage lookups based on Presales Involvement Aux.",
|
|
examples: ["--manager-email-list resource.manager.a@oracle.com,resource.manager.b@oracle.com"],
|
|
},
|
|
{
|
|
name: "owner-email",
|
|
cli: "--owner-email",
|
|
kind: "string",
|
|
valueLabel: "<user@oracle.com>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["resource", "sa-attach"],
|
|
description: "Direct territory-owner email filter for workload and pipeline reads.",
|
|
examples: ["--owner-email territory.owner@oracle.com"],
|
|
},
|
|
{
|
|
name: "owner-manager-email",
|
|
cli: "--owner-manager-email",
|
|
kind: "string",
|
|
valueLabel: "<user@oracle.com>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["resource", "sa-attach"],
|
|
description: "Upper territory-owner manager email filter for workload and pipeline reads.",
|
|
examples: ["--owner-manager-email territory.manager@oracle.com"],
|
|
},
|
|
{
|
|
name: "match-role",
|
|
cli: "--match-role",
|
|
kind: "enum",
|
|
valueLabel: "team|lead",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["resource"],
|
|
default: "team",
|
|
values: ["team", "lead"],
|
|
description: "Use team for SR Team Level 1..4 matching, or lead for Service Request Lead Email.",
|
|
},
|
|
{
|
|
name: "sr-status",
|
|
cli: "--sr-status",
|
|
kind: "csv",
|
|
valueLabel: "Open,Closed",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["resource"],
|
|
description: "Optional SR status filter for resource lookups.",
|
|
},
|
|
{
|
|
name: "cluster",
|
|
cli: "--cluster",
|
|
kind: "string",
|
|
valueLabel: "<cluster>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["intent", "date-window", "resource", "sa-attach"],
|
|
default: SA_ATTACH_DEFAULT_CLUSTER,
|
|
description:
|
|
"Commercial Cluster filter. SA Attach and workload-manager intents default to Brazil; close-window opportunity intents apply it only when explicit.",
|
|
},
|
|
{
|
|
name: "solution",
|
|
cli: "--solution",
|
|
kind: "csv",
|
|
valueLabel: "valueA,valueB",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["sa-attach"],
|
|
default: SA_ATTACH_DEFAULT_SOLUTIONS,
|
|
description: "SA Attach solution filter.",
|
|
},
|
|
{
|
|
name: "revenue-line-status",
|
|
cli: "--revenue-line-status",
|
|
kind: "csv",
|
|
valueLabel: "Open,Won,Won Pending",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["sa-attach"],
|
|
default: SA_ATTACH_DEFAULT_ALL_STATUSES,
|
|
description: "SA Attach revenue-line status filter.",
|
|
},
|
|
{
|
|
name: "executive-product-lob",
|
|
cli: "--executive-product-lob",
|
|
kind: "csv",
|
|
valueLabel: "Cloud Infrastructure,Hardware",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["sa-attach"],
|
|
default: SA_ATTACH_DEFAULT_PRODUCT_LOBS,
|
|
description: "SA Attach executive product LOB filter.",
|
|
},
|
|
{
|
|
name: "revenue-type-group",
|
|
cli: "--revenue-type-group",
|
|
kind: "csv",
|
|
valueLabel: "NEW,EXPANSION,WORKLOAD",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["sa-attach"],
|
|
default: SA_ATTACH_DEFAULT_REVENUE_TYPE_GROUPS,
|
|
description: "SA Attach revenue type group filter.",
|
|
},
|
|
{
|
|
name: "sales-credit-type",
|
|
cli: "--sales-credit-type",
|
|
kind: "string",
|
|
valueLabel: "<type>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["sa-attach"],
|
|
default: SA_ATTACH_DEFAULT_SALES_CREDIT_TYPE,
|
|
description: "SA Attach sales credit type filter.",
|
|
},
|
|
{
|
|
name: "min-opportunity-probability",
|
|
cli: "--min-opportunity-probability",
|
|
kind: "number",
|
|
valueLabel: "<number>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["sa-attach"],
|
|
default: SA_ATTACH_DEFAULT_MIN_OPPORTUNITY_PROBABILITY,
|
|
description: "Minimum opportunity probability for SA Attach views.",
|
|
},
|
|
{
|
|
name: "forecast-type",
|
|
cli: "--forecast-type",
|
|
kind: "csv",
|
|
valueLabel: "Forecast,Upside,Won",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["sa-attach"],
|
|
description: "SA Attach revenue-line forecast type group filter.",
|
|
},
|
|
{
|
|
name: "limit",
|
|
cli: "--limit",
|
|
kind: "number",
|
|
valueLabel: "<rows>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution"],
|
|
default: 5000,
|
|
description: "Maximum rows to request from Oracle DV.",
|
|
},
|
|
{
|
|
name: "fiscal-year",
|
|
cli: "--fiscal-year",
|
|
kind: "string",
|
|
valueLabel: "FY26",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution"],
|
|
description: "Override the fiscal year for views that need one.",
|
|
},
|
|
{
|
|
name: "scope",
|
|
cli: "--scope",
|
|
kind: "enum",
|
|
valueLabel: "auto|org-wide|my-team",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution", "query", "view", "intent"],
|
|
default: SCOPE_AUTO,
|
|
values: [SCOPE_AUTO, SCOPE_ORG_WIDE, "my-team"],
|
|
description:
|
|
"Controls source scope. auto is org-wide-first for org-wide-capable XSA datasets and my-team-only for DV - SE Team fields.",
|
|
},
|
|
{
|
|
name: "runtime-home",
|
|
cli: "--runtime-home",
|
|
kind: "path",
|
|
valueLabel: "<dir>",
|
|
appliesTo: [
|
|
"fetch-sales-intelligence.mjs",
|
|
"refresh-auth-state.mjs",
|
|
"refresh-auth-direct.mjs",
|
|
],
|
|
env: "ORACLE_SI_RUNTIME_HOME",
|
|
default: DISPLAY_RUNTIME_HOME,
|
|
description:
|
|
"Override the workspace runtime root. By default the skill uses <workspace>/knowledge-one-view-cli/runtime and only falls back to legacy ~/.codex state for reads when the new workspace runtime is still empty.",
|
|
},
|
|
{
|
|
name: "state-path",
|
|
cli: "--state-path",
|
|
kind: "path",
|
|
valueLabel: "<file>",
|
|
appliesTo: [
|
|
"fetch-sales-intelligence.mjs",
|
|
"refresh-auth-state.mjs",
|
|
"refresh-auth-direct.mjs",
|
|
],
|
|
env: "ORACLE_SI_STATE_PATH",
|
|
default: DISPLAY_STATE_PATH,
|
|
description:
|
|
"Override the auth-state.json path. Without this override, writes go to the workspace runtime and reads can still fall back to legacy ~/.codex state if it already exists.",
|
|
},
|
|
{
|
|
name: "meta-path",
|
|
cli: "--meta-path",
|
|
kind: "path",
|
|
valueLabel: "<file>",
|
|
appliesTo: [
|
|
"fetch-sales-intelligence.mjs",
|
|
"refresh-auth-state.mjs",
|
|
"refresh-auth-direct.mjs",
|
|
],
|
|
env: "ORACLE_SI_META_PATH",
|
|
default: DISPLAY_META_PATH,
|
|
description:
|
|
"Override the session-meta.json path. Without this override, writes go to the workspace runtime and reads can still fall back to legacy ~/.codex state if it already exists.",
|
|
},
|
|
{
|
|
name: "base-url",
|
|
cli: "--base-url",
|
|
kind: "string",
|
|
valueLabel: "<url>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
env: "ORACLE_SI_BASE_URL",
|
|
default: DEFAULT_BASE_URL,
|
|
description: "Override the Oracle DV base URL.",
|
|
},
|
|
{
|
|
name: "no-cache",
|
|
cli: "--no-cache",
|
|
kind: "flag",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution"],
|
|
default: false,
|
|
description: "Disable cached auth/session refresh logic for this invocation.",
|
|
},
|
|
{
|
|
name: "output-file",
|
|
cli: "--output-file",
|
|
kind: "path",
|
|
valueLabel: "<file>",
|
|
appliesTo: ["fetch-sales-intelligence.mjs"],
|
|
modes: ["execution"],
|
|
description:
|
|
"Write the JSON payload to a file as well as stdout. Relative paths without a directory are written under <workspace>/knowledge-one-view-cli/output/; other relative paths are resolved under the workspace skill folder.",
|
|
},
|
|
{
|
|
name: "profile-dir",
|
|
cli: "--profile-dir",
|
|
kind: "path",
|
|
valueLabel: "<dir>",
|
|
appliesTo: ["refresh-auth-state.mjs"],
|
|
env: "ORACLE_SI_PROFILE_DIR",
|
|
default: DISPLAY_PROFILE_DIR,
|
|
description: "Override the Chrome user-data directory for browser-based auth refresh.",
|
|
},
|
|
{
|
|
name: "chrome-path",
|
|
cli: "--chrome-path",
|
|
kind: "path",
|
|
valueLabel: "<file>",
|
|
appliesTo: ["refresh-auth-state.mjs"],
|
|
env: "ORACLE_SI_CHROME_PATH",
|
|
default: DISPLAY_CHROME_PATH,
|
|
description: "Override the Chrome executable used for browser-based auth refresh.",
|
|
},
|
|
{
|
|
name: "timeout-ms",
|
|
cli: "--timeout-ms",
|
|
kind: "number",
|
|
valueLabel: "<milliseconds>",
|
|
appliesTo: ["refresh-auth-state.mjs"],
|
|
default: 600000,
|
|
description: "Maximum wait for the Oracle DV page to become authenticated.",
|
|
},
|
|
{
|
|
name: "url",
|
|
cli: "--url",
|
|
kind: "string",
|
|
valueLabel: "<url>",
|
|
appliesTo: ["refresh-auth-state.mjs", "refresh-auth-direct.mjs"],
|
|
env: "ORACLE_SI_TARGET_URL",
|
|
description: "Override the target Oracle DV workbook URL.",
|
|
},
|
|
{
|
|
name: "username",
|
|
cli: "--username",
|
|
kind: "string",
|
|
valueLabel: "<email>",
|
|
appliesTo: ["refresh-auth-direct.mjs"],
|
|
env: "ORACLE_SI_USERNAME",
|
|
default: "prompted if omitted",
|
|
description: "Username used by the direct-auth flow.",
|
|
},
|
|
{
|
|
name: "factor",
|
|
cli: "--factor",
|
|
kind: "enum",
|
|
valueLabel: "<factor>",
|
|
appliesTo: ["refresh-auth-direct.mjs"],
|
|
values: ["PUSH", "SMS", "EMAIL", "PHONE_CALL", "TOTP", "BYPASSCODE", "PASSWORD", "FIDO_PASSKEY"],
|
|
description: "Preferred MFA factor for direct auth.",
|
|
},
|
|
{
|
|
name: "help",
|
|
cli: "--help",
|
|
kind: "flag",
|
|
appliesTo: [
|
|
"fetch-sales-intelligence.mjs",
|
|
"refresh-auth-state.mjs",
|
|
"refresh-auth-direct.mjs",
|
|
],
|
|
default: false,
|
|
description: "Show CLI usage help.",
|
|
},
|
|
];
|
|
|
|
const SCRIPT_CATALOG = [
|
|
{
|
|
name: "fetch-sales-intelligence.mjs",
|
|
role: "Primary backend retrieval script for Oracle DV views, intents, query mode, and custom SQL.",
|
|
outputs: ["JSON to stdout", "Optional JSON file via --output-file"],
|
|
parameterNames: PARAMETER_CATALOG
|
|
.filter((item) => item.appliesTo.includes("fetch-sales-intelligence.mjs"))
|
|
.map((item) => item.name),
|
|
},
|
|
{
|
|
name: "refresh-auth-state.mjs",
|
|
role: "Optional browser-assisted development helper that captures cookies and DV session headers from Chrome when direct auth is not enough.",
|
|
outputs: ["auth-state.json", "session-meta.json"],
|
|
parameterNames: PARAMETER_CATALOG
|
|
.filter((item) => item.appliesTo.includes("refresh-auth-state.mjs"))
|
|
.map((item) => item.name),
|
|
},
|
|
{
|
|
name: "refresh-auth-direct.mjs",
|
|
role: "Direct Oracle sign-in flow without opening Chrome, including MFA selection.",
|
|
outputs: ["auth-state.json", "session-meta.json"],
|
|
parameterNames: PARAMETER_CATALOG
|
|
.filter((item) => item.appliesTo.includes("refresh-auth-direct.mjs"))
|
|
.map((item) => item.name),
|
|
},
|
|
];
|
|
|
|
function usage() {
|
|
return `Fetch Oracle Sales Intelligence data directly from Oracle DV.
|
|
|
|
Usage:
|
|
node fetch-sales-intelligence.mjs --catalog
|
|
node fetch-sales-intelligence.mjs --list-parameters
|
|
node fetch-sales-intelligence.mjs --describe-parameter resource
|
|
node fetch-sales-intelligence.mjs --list-scripts
|
|
node fetch-sales-intelligence.mjs --describe-script fetch-sales-intelligence.mjs
|
|
node fetch-sales-intelligence.mjs --check-auth
|
|
node fetch-sales-intelligence.mjs --query --select opportunityId,opportunityName
|
|
node fetch-sales-intelligence.mjs --list-views
|
|
node fetch-sales-intelligence.mjs --describe-view <view>
|
|
node fetch-sales-intelligence.mjs --list-intents
|
|
node fetch-sales-intelligence.mjs --describe-intent <intent>
|
|
node fetch-sales-intelligence.mjs --render-sql --sql "<logical sql template>"
|
|
node fetch-sales-intelligence.mjs --view <view> [options]
|
|
node fetch-sales-intelligence.mjs --intent <intent> [options]
|
|
node fetch-sales-intelligence.mjs --sql "<logical sql>"
|
|
|
|
Fast paths:
|
|
--intent opportunities-default --limit 200
|
|
--query --select opportunityId,opportunityName,closeDate,bookingValue,workloadAmount --where "closeDate = DATE '2026-05-02'"
|
|
--query --select opportunityId,opportunityName,closeDate,pipelineAmount --where "closeDate = DATE '2026-05-02'"
|
|
--intent opportunities-close-date --date 2026-05-02
|
|
--intent opportunities-close-month --month 2026-04
|
|
--intent opportunities-current-team-close-window --from-date today --to-date +4d
|
|
--intent opportunities-by-manager-close-window --manager-email resource.manager@oracle.com --week w2 --week-year 2026
|
|
--intent workloads-by-manager-resource-close-window --manager-email-list resource.manager.a@oracle.com,resource.manager.b@oracle.com --from-date today --to-date +7d
|
|
--intent srs-by-resource --resource resource.member
|
|
|
|
Direct view shortcuts:
|
|
--view opportunities-close-window --date 2026-05-02
|
|
--view opportunities-close-window --month 2026-04
|
|
--view srs-by-resource-email --resource resource.member
|
|
|
|
Notes:
|
|
--query infers the backend source from selected aliases and uses org-wide-capable XSA sources before DV - SE Team
|
|
--scope auto|org-wide|my-team controls whether org-wide-capable XSA views add the current-team filter
|
|
--resource accepts resource.member or resource.member@oracle.com
|
|
--date resolves one exact day
|
|
--month resolves the first and last day automatically
|
|
--week resolves annual weeks anchored on June 1 and continuing through the following May
|
|
current-team manager/resource asks should prefer --intent opportunities-current-team-close-window
|
|
explicit resource-scoped opportunity asks may use --intent opportunities-by-resource-close-window --resource <uid>
|
|
explicit manager-scoped opportunity asks may use --intent opportunities-by-manager-close-window --manager-email <user@oracle.com>
|
|
org-wide workload/resource asks may use --intent workloads-by-manager-resource-close-window --manager-email-list <emailA,emailB>
|
|
--match-role defaults to team, not lead
|
|
--measure accepts specs like sum:bookingValue:totalBooking or count-distinct:opportunityId:optyCount
|
|
custom SQL may use placeholders like {{field:closeDate}} and {{macro:securityEntryPointFilter}}
|
|
`;
|
|
}
|
|
|
|
async function withConsoleLogRoutedToStderr(callback) {
|
|
const originalLog = console.log;
|
|
console.log = (...args) => console.error(...args);
|
|
try {
|
|
return await callback();
|
|
} finally {
|
|
console.log = originalLog;
|
|
}
|
|
}
|
|
|
|
async function runInteractiveAuthRefresh({ statePath, metaPath, targetUrl = null }) {
|
|
const runtimeHome = inferRuntimeHome(statePath, metaPath);
|
|
const refreshModule = await import(new URL("./refresh-auth-state.mjs", import.meta.url));
|
|
if (typeof refreshModule.main !== "function") {
|
|
throw new Error("Could not load refresh-auth-state.mjs as an importable helper.");
|
|
}
|
|
|
|
const argv = [];
|
|
if (runtimeHome) {
|
|
argv.push("--runtime-home", runtimeHome);
|
|
}
|
|
if (statePath) {
|
|
argv.push("--state-path", statePath);
|
|
}
|
|
if (metaPath) {
|
|
argv.push("--meta-path", metaPath);
|
|
}
|
|
if (targetUrl) {
|
|
argv.push("--url", targetUrl);
|
|
}
|
|
|
|
console.error("Oracle DV session is missing or expired. Opening browser login to refresh auth...");
|
|
await withConsoleLogRoutedToStderr(() => refreshModule.main(argv));
|
|
}
|
|
|
|
async function reloadSessionArtifacts({ statePath, metaPath, state, meta }) {
|
|
const refreshedState = await readJson(statePath, "auth state");
|
|
const refreshedMeta = normalizeMeta(metaPath, await readJson(metaPath, "session metadata"));
|
|
replaceObjectContents(state, refreshedState);
|
|
replaceObjectContents(meta, refreshedMeta);
|
|
return { state, meta };
|
|
}
|
|
|
|
async function loadSessionArtifacts({
|
|
statePath,
|
|
metaPath,
|
|
targetUrl = null,
|
|
allowInteractiveRefresh = true,
|
|
}) {
|
|
try {
|
|
const state = await readJson(statePath, "auth state");
|
|
const meta = normalizeMeta(metaPath, await readJson(metaPath, "session metadata"));
|
|
return { state, meta };
|
|
} catch (error) {
|
|
if (!allowInteractiveRefresh || !isAuthRefreshRequiredError(error)) {
|
|
throw error;
|
|
}
|
|
await runInteractiveAuthRefresh({ statePath, metaPath, targetUrl });
|
|
const state = await readJson(statePath, "auth state");
|
|
const meta = normalizeMeta(metaPath, await readJson(metaPath, "session metadata"));
|
|
return { state, meta };
|
|
}
|
|
}
|
|
|
|
function hasFreshFiscalYearCache(meta) {
|
|
if (!meta?.currentFiscalYear || !meta?.currentFiscalYearCapturedAt) {
|
|
return false;
|
|
}
|
|
const capturedAtMs = Date.parse(meta.currentFiscalYearCapturedAt);
|
|
if (!Number.isFinite(capturedAtMs)) {
|
|
return false;
|
|
}
|
|
return Date.now() - capturedAtMs <= FISCAL_YEAR_CACHE_MAX_AGE_MS;
|
|
}
|
|
|
|
function hasFreshSessionContextCache(meta) {
|
|
if (!meta?.currentSessionContext || !meta?.currentSessionContextCapturedAt) {
|
|
return false;
|
|
}
|
|
const capturedAtMs = Date.parse(meta.currentSessionContextCapturedAt);
|
|
if (!Number.isFinite(capturedAtMs)) {
|
|
return false;
|
|
}
|
|
return Date.now() - capturedAtMs <= SESSION_CONTEXT_CACHE_MAX_AGE_MS;
|
|
}
|
|
|
|
function normalizeScopeInput(value = SCOPE_AUTO) {
|
|
const normalized = String(value || SCOPE_AUTO).trim().toLowerCase();
|
|
if (!normalized || normalized === SCOPE_AUTO) return SCOPE_AUTO;
|
|
if (["org-wide", "orgwide", "organization", "organisation"].includes(normalized)) {
|
|
return SCOPE_ORG_WIDE;
|
|
}
|
|
if (["my-team", "my-team-only", "team", "current-team", "current_team"].includes(normalized)) {
|
|
return SCOPE_MY_TEAM;
|
|
}
|
|
throw new Error(`Unsupported --scope "${value}". Use auto, org-wide, or my-team.`);
|
|
}
|
|
|
|
function parseLimit(value, fallback = 5000) {
|
|
const raw = String(value || fallback).trim();
|
|
if (!/^\d+$/.test(raw)) {
|
|
throw new Error(`Unsupported --limit "${raw}". Use a positive integer.`);
|
|
}
|
|
const parsed = Number(raw);
|
|
if (!Number.isSafeInteger(parsed) || parsed <= 0) {
|
|
throw new Error(`Unsupported --limit "${raw}". Use a positive integer.`);
|
|
}
|
|
return parsed;
|
|
}
|
|
|
|
function resolveDatasetPolicy(sourceName) {
|
|
if (!sourceName) return null;
|
|
return DATASET_REGISTRY[sourceName] || null;
|
|
}
|
|
|
|
function viewScopePolicy(viewName) {
|
|
return VIEW_SCOPE_REGISTRY[viewName] || {};
|
|
}
|
|
|
|
function sourceNameForView(viewName, definition = {}, { source = null, fallbackUsed = false } = {}) {
|
|
if (source) return source;
|
|
if (fallbackUsed && definition?.fallbackSource) return definition.fallbackSource;
|
|
if (definition?.primarySource) return definition.primarySource;
|
|
return viewScopePolicy(viewName).sourceName || null;
|
|
}
|
|
|
|
function resolveEffectiveScope({
|
|
requestedScope = SCOPE_AUTO,
|
|
sourceName = null,
|
|
viewName = null,
|
|
definition = {},
|
|
fallbackUsed = false,
|
|
requiresMyTeam = false,
|
|
} = {}) {
|
|
if (fallbackUsed && definition?.fallbackScope === SCOPE_MY_TEAM) {
|
|
return SCOPE_MY_TEAM;
|
|
}
|
|
const policy = viewScopePolicy(viewName);
|
|
if (requiresMyTeam || policy.requiresMyTeam) {
|
|
return SCOPE_MY_TEAM;
|
|
}
|
|
const resolvedSourceName = sourceName || sourceNameForView(viewName, definition);
|
|
const datasetPolicy = resolveDatasetPolicy(resolvedSourceName);
|
|
if (!datasetPolicy) {
|
|
return SCOPE_UNKNOWN;
|
|
}
|
|
if (datasetPolicy.backendCapability === BACKEND_MY_TEAM_ONLY) {
|
|
return SCOPE_MY_TEAM;
|
|
}
|
|
const normalizedRequest = normalizeScopeInput(requestedScope);
|
|
if (normalizedRequest === SCOPE_MY_TEAM) {
|
|
return datasetPolicy.allowedScopes.includes(SCOPE_MY_TEAM)
|
|
? SCOPE_MY_TEAM
|
|
: datasetPolicy.defaultScope;
|
|
}
|
|
if (normalizedRequest === SCOPE_ORG_WIDE) {
|
|
return datasetPolicy.allowedScopes.includes(SCOPE_ORG_WIDE)
|
|
? SCOPE_ORG_WIDE
|
|
: datasetPolicy.defaultScope;
|
|
}
|
|
return datasetPolicy.defaultScope || SCOPE_UNKNOWN;
|
|
}
|
|
|
|
function buildXsaScopePredicates(dataset, effectiveScope) {
|
|
return effectiveScope === SCOPE_MY_TEAM ? [buildXsaTeamFilter(dataset)] : [];
|
|
}
|
|
|
|
function buildScopeComponent({ name, label = null, source = null, scope = SCOPE_UNKNOWN }) {
|
|
return {
|
|
name,
|
|
...(label ? { label } : {}),
|
|
source,
|
|
scope,
|
|
};
|
|
}
|
|
|
|
function previousFiscalYear(fiscalYear) {
|
|
const match = /^FY(\d+)$/.exec(String(fiscalYear));
|
|
if (!match) return null;
|
|
const width = match[1].length;
|
|
const numeric = Number(match[1]);
|
|
if (!Number.isFinite(numeric)) return null;
|
|
return `FY${String(numeric - 1).padStart(width, "0")}`;
|
|
}
|
|
|
|
function fiscalYearToCloseDateWindow(fiscalYear) {
|
|
const raw = String(fiscalYear || "").trim().toUpperCase();
|
|
if (!raw) return null;
|
|
const digits = raw.replace(/^FY/, "");
|
|
if (!/^\d{2,4}$/.test(digits)) return null;
|
|
const endYear = digits.length === 2 ? 2000 + Number(digits) : Number(digits);
|
|
if (!Number.isFinite(endYear) || endYear < 2000) return null;
|
|
return {
|
|
fromDate: `${endYear - 1}-06-01`,
|
|
toDate: `${endYear}-05-31`,
|
|
};
|
|
}
|
|
|
|
function viewNames() {
|
|
return Object.keys(VIEW_DEFS).sort();
|
|
}
|
|
|
|
function intentNames() {
|
|
return Object.keys(INTENT_CATALOG).sort();
|
|
}
|
|
|
|
function macroNames() {
|
|
return Object.keys(MACRO_CATALOG).sort();
|
|
}
|
|
|
|
function levenshtein(a, b) {
|
|
const rows = Array.from({ length: a.length + 1 }, (_, index) => [index]);
|
|
for (let column = 1; column <= b.length; column += 1) {
|
|
rows[0][column] = column;
|
|
}
|
|
for (let row = 1; row <= a.length; row += 1) {
|
|
for (let column = 1; column <= b.length; column += 1) {
|
|
const substitutionCost = a[row - 1] === b[column - 1] ? 0 : 1;
|
|
rows[row][column] = Math.min(
|
|
rows[row - 1][column] + 1,
|
|
rows[row][column - 1] + 1,
|
|
rows[row - 1][column - 1] + substitutionCost
|
|
);
|
|
}
|
|
}
|
|
return rows[a.length][b.length];
|
|
}
|
|
|
|
function suggestViewName(input) {
|
|
const normalizedInput = String(input || "").trim().toLowerCase();
|
|
if (!normalizedInput) return null;
|
|
let best = null;
|
|
for (const candidate of viewNames()) {
|
|
const score = levenshtein(normalizedInput, candidate.toLowerCase());
|
|
if (!best || score < best.score) {
|
|
best = { name: candidate, score };
|
|
}
|
|
}
|
|
return best && best.score <= 8 ? best.name : null;
|
|
}
|
|
|
|
function suggestItemName(input, items) {
|
|
const normalizedInput = String(input || "").trim().toLowerCase();
|
|
if (!normalizedInput) return null;
|
|
let best = null;
|
|
for (const candidate of items) {
|
|
const score = levenshtein(normalizedInput, String(candidate).toLowerCase());
|
|
if (!best || score < best.score) {
|
|
best = { name: candidate, score };
|
|
}
|
|
}
|
|
return best && best.score <= 8 ? best.name : null;
|
|
}
|
|
|
|
function summarizeView(name, definition) {
|
|
return {
|
|
name,
|
|
description: definition.description || null,
|
|
primarySource: definition.primarySource || null,
|
|
fallbackSource: definition.fallbackSource || null,
|
|
fallbackScope: definition.fallbackScope || null,
|
|
entities: definition.entities || [],
|
|
fields: definition.fields || [],
|
|
defaultLimit: definition.defaultLimit ?? 1,
|
|
defaultStatuses: definition.defaultStatuses || [],
|
|
needsFiscalYear: definition.needsFiscalYear !== false,
|
|
parameters: definition.parameters || [],
|
|
scope: buildViewScopeDescriptor(name, definition),
|
|
};
|
|
}
|
|
|
|
function summarizeIntent(name, definition) {
|
|
return {
|
|
name,
|
|
description: definition.description || null,
|
|
view: definition.view,
|
|
parameters: definition.parameters || [],
|
|
examples: definition.examples || [],
|
|
scope: buildIntentScopeDescriptor(name, definition),
|
|
};
|
|
}
|
|
|
|
function summarizeMacro(name, definition) {
|
|
return {
|
|
name,
|
|
description: definition.description || null,
|
|
value: definition.resolve(),
|
|
};
|
|
}
|
|
|
|
function summarizeCatalog() {
|
|
return {
|
|
views: viewNames().map((name) => summarizeView(name, VIEW_DEFS[name])),
|
|
intents: intentNames().map((name) => summarizeIntent(name, INTENT_CATALOG[name])),
|
|
entities: summarizeEntities(),
|
|
fields: listFields(),
|
|
macros: macroNames().map((name) => summarizeMacro(name, MACRO_CATALOG[name])),
|
|
parameters: summarizeParameters(),
|
|
scripts: summarizeScripts(),
|
|
};
|
|
}
|
|
|
|
function summarizeParameter(parameter) {
|
|
return {
|
|
name: parameter.name,
|
|
cli: parameter.cli,
|
|
kind: parameter.kind,
|
|
...(parameter.valueLabel ? { valueLabel: parameter.valueLabel } : {}),
|
|
appliesTo: parameter.appliesTo || [],
|
|
...(parameter.modes ? { modes: parameter.modes } : {}),
|
|
...(Object.prototype.hasOwnProperty.call(parameter, "default")
|
|
? { default: parameter.default }
|
|
: {}),
|
|
...(parameter.env ? { env: parameter.env } : {}),
|
|
...(parameter.values ? { values: parameter.values } : {}),
|
|
...(parameter.onlyWith ? { onlyWith: parameter.onlyWith } : {}),
|
|
...(parameter.conflictsWith ? { conflictsWith: parameter.conflictsWith } : {}),
|
|
description: parameter.description || null,
|
|
...(parameter.examples ? { examples: parameter.examples } : {}),
|
|
};
|
|
}
|
|
|
|
function summarizeParameters() {
|
|
return [...PARAMETER_CATALOG]
|
|
.sort((left, right) => left.cli.localeCompare(right.cli))
|
|
.map((parameter) => summarizeParameter(parameter));
|
|
}
|
|
|
|
function findParameter(token) {
|
|
const normalized = String(token || "").trim().toLowerCase();
|
|
if (!normalized) return null;
|
|
return (
|
|
PARAMETER_CATALOG.find((parameter) => parameter.name.toLowerCase() === normalized) ||
|
|
PARAMETER_CATALOG.find((parameter) => parameter.cli.toLowerCase() === normalized)
|
|
);
|
|
}
|
|
|
|
function summarizeScript(script) {
|
|
return {
|
|
name: script.name,
|
|
role: script.role,
|
|
outputs: script.outputs || [],
|
|
parameters: script.parameterNames
|
|
.map((name) => PARAMETER_CATALOG.find((parameter) => parameter.name === name))
|
|
.filter(Boolean)
|
|
.map((parameter) => summarizeParameter(parameter)),
|
|
};
|
|
}
|
|
|
|
function summarizeScripts() {
|
|
return SCRIPT_CATALOG.map((script) => summarizeScript(script));
|
|
}
|
|
|
|
function findScript(name) {
|
|
const normalized = String(name || "").trim().toLowerCase();
|
|
return SCRIPT_CATALOG.find((script) => script.name.toLowerCase() === normalized) || null;
|
|
}
|
|
|
|
function summarizeEntities() {
|
|
const entities = new Map();
|
|
for (const [viewName, definition] of Object.entries(VIEW_DEFS)) {
|
|
for (const entity of definition.entities || []) {
|
|
if (!entities.has(entity)) {
|
|
entities.set(entity, []);
|
|
}
|
|
entities.get(entity).push(viewName);
|
|
}
|
|
}
|
|
return Array.from(entities.entries())
|
|
.sort(([left], [right]) => left.localeCompare(right))
|
|
.map(([entity, views]) => ({
|
|
entity,
|
|
views: views.sort(),
|
|
}));
|
|
}
|
|
|
|
function summarizeField(field) {
|
|
return {
|
|
alias: field.alias,
|
|
entity: field.entity,
|
|
expr: field.expr,
|
|
kind: field.kind,
|
|
roles: field.roles || [],
|
|
description: field.description || null,
|
|
};
|
|
}
|
|
|
|
function listFields({ entity = null, role = null, search = null } = {}) {
|
|
const normalizedEntity = entity ? String(entity).trim().toLowerCase() : null;
|
|
const normalizedRole = role ? String(role).trim().toLowerCase() : null;
|
|
const normalizedSearch = search ? String(search).trim().toLowerCase() : null;
|
|
return FIELD_CATALOG
|
|
.filter((field) => {
|
|
if (normalizedEntity && field.entity.toLowerCase() !== normalizedEntity) return false;
|
|
if (normalizedRole && !(field.roles || []).some((item) => item.toLowerCase() === normalizedRole)) {
|
|
return false;
|
|
}
|
|
if (
|
|
normalizedSearch &&
|
|
![field.alias, field.entity, field.expr, field.description]
|
|
.filter(Boolean)
|
|
.some((value) => String(value).toLowerCase().includes(normalizedSearch))
|
|
) {
|
|
return false;
|
|
}
|
|
return true;
|
|
})
|
|
.sort((left, right) =>
|
|
`${left.entity}|${left.alias}`.localeCompare(`${right.entity}|${right.alias}`)
|
|
)
|
|
.map(summarizeField);
|
|
}
|
|
|
|
function findField(input, fieldCatalog = FIELD_CATALOG) {
|
|
const normalized = String(input || "").trim().toLowerCase();
|
|
if (!normalized) return null;
|
|
return (
|
|
fieldCatalog.find((field) => field.alias.toLowerCase() === normalized) ||
|
|
fieldCatalog.find((field) => field.expr.toLowerCase() === normalized) ||
|
|
fieldCatalog.find((field) => `${field.entity}.${field.alias}`.toLowerCase() === normalized) ||
|
|
null
|
|
);
|
|
}
|
|
|
|
function findMacro(input) {
|
|
const normalized = String(input || "").trim();
|
|
if (!normalized) return null;
|
|
return MACRO_CATALOG[normalized] || null;
|
|
}
|
|
|
|
function normalizeColumnAlias(value) {
|
|
const raw = String(value || "").trim();
|
|
if (!raw) return null;
|
|
if (/^".*"$/.test(raw)) {
|
|
return raw.slice(1, -1);
|
|
}
|
|
return raw;
|
|
}
|
|
|
|
function describeTemplateToken(token) {
|
|
const raw = String(token || "").trim();
|
|
if (!raw) {
|
|
throw new Error("Empty SQL template token.");
|
|
}
|
|
const fieldPrefixed = /^field:(.+)$/i.exec(raw);
|
|
if (fieldPrefixed) {
|
|
const field = findField(fieldPrefixed[1]);
|
|
if (!field) {
|
|
const suggestion = suggestItemName(fieldPrefixed[1], FIELD_CATALOG.map((item) => item.alias));
|
|
throw new Error(
|
|
suggestion
|
|
? `Unknown field token "${fieldPrefixed[1]}". Did you mean "${suggestion}"?`
|
|
: `Unknown field token "${fieldPrefixed[1]}". Use --catalog or --list-fields to inspect supported field aliases.`
|
|
);
|
|
}
|
|
return {
|
|
kind: "field",
|
|
token: raw,
|
|
name: field.alias,
|
|
resolved: field.expr,
|
|
};
|
|
}
|
|
const macroPrefixed = /^macro:(.+)$/i.exec(raw);
|
|
if (macroPrefixed) {
|
|
const macro = findMacro(macroPrefixed[1]);
|
|
if (!macro) {
|
|
const suggestion = suggestItemName(macroPrefixed[1], macroNames());
|
|
throw new Error(
|
|
suggestion
|
|
? `Unknown macro token "${macroPrefixed[1]}". Did you mean "${suggestion}"?`
|
|
: `Unknown macro token "${macroPrefixed[1]}". Use --catalog to inspect supported macros.`
|
|
);
|
|
}
|
|
return {
|
|
kind: "macro",
|
|
token: raw,
|
|
name: macroPrefixed[1],
|
|
resolved: macro.resolve(),
|
|
};
|
|
}
|
|
const field = findField(raw);
|
|
if (field) {
|
|
return {
|
|
kind: "field",
|
|
token: raw,
|
|
name: field.alias,
|
|
resolved: field.expr,
|
|
};
|
|
}
|
|
const macro = findMacro(raw);
|
|
if (macro) {
|
|
return {
|
|
kind: "macro",
|
|
token: raw,
|
|
name: raw,
|
|
resolved: macro.resolve(),
|
|
};
|
|
}
|
|
const fieldSuggestion = suggestItemName(raw, FIELD_CATALOG.map((item) => item.alias));
|
|
const macroSuggestion = suggestItemName(raw, macroNames());
|
|
const suggestion = fieldSuggestion || macroSuggestion;
|
|
throw new Error(
|
|
suggestion
|
|
? `Unknown SQL template token "${raw}". Did you mean "${suggestion}"?`
|
|
: `Unknown SQL template token "${raw}". Use --catalog to inspect supported field aliases and macros.`
|
|
);
|
|
}
|
|
|
|
function resolveTemplateToken(token) {
|
|
return describeTemplateToken(token).resolved;
|
|
}
|
|
|
|
function expandSqlTemplate(sql) {
|
|
const source = String(sql || "");
|
|
const tokens = [];
|
|
const expandedSql = source.replace(/\{\{([^{}]+)\}\}/g, (_, token) => {
|
|
const described = describeTemplateToken(token);
|
|
tokens.push({
|
|
token: described.token,
|
|
kind: described.kind,
|
|
name: described.name,
|
|
resolved: described.resolved,
|
|
});
|
|
return described.resolved;
|
|
});
|
|
return {
|
|
sql: expandedSql,
|
|
templateTokens: tokens,
|
|
};
|
|
}
|
|
|
|
function indexOfKeywordAtTopLevel(sql, keyword, startIndex = 0) {
|
|
const target = String(keyword || "").toUpperCase();
|
|
let inSingle = false;
|
|
let inDouble = false;
|
|
let depth = 0;
|
|
for (let index = startIndex; index < sql.length; index += 1) {
|
|
const char = sql[index];
|
|
const next = sql[index + 1];
|
|
if (inSingle) {
|
|
if (char === "'" && next === "'") {
|
|
index += 1;
|
|
continue;
|
|
}
|
|
if (char === "'") inSingle = false;
|
|
continue;
|
|
}
|
|
if (inDouble) {
|
|
if (char === '"') inDouble = false;
|
|
continue;
|
|
}
|
|
if (char === "'") {
|
|
inSingle = true;
|
|
continue;
|
|
}
|
|
if (char === '"') {
|
|
inDouble = true;
|
|
continue;
|
|
}
|
|
if (char === "(") {
|
|
depth += 1;
|
|
continue;
|
|
}
|
|
if (char === ")") {
|
|
depth = Math.max(0, depth - 1);
|
|
continue;
|
|
}
|
|
if (depth > 0) continue;
|
|
if (sql.slice(index, index + target.length).toUpperCase() !== target) continue;
|
|
const previous = index === 0 ? " " : sql[index - 1];
|
|
const following = sql[index + target.length] || " ";
|
|
if (/[A-Z0-9_]/i.test(previous) || /[A-Z0-9_]/i.test(following)) continue;
|
|
return index;
|
|
}
|
|
return -1;
|
|
}
|
|
|
|
function splitTopLevelComma(text) {
|
|
const items = [];
|
|
let inSingle = false;
|
|
let inDouble = false;
|
|
let depth = 0;
|
|
let current = "";
|
|
for (let index = 0; index < text.length; index += 1) {
|
|
const char = text[index];
|
|
const next = text[index + 1];
|
|
current += char;
|
|
if (inSingle) {
|
|
if (char === "'" && next === "'") {
|
|
current += next;
|
|
index += 1;
|
|
continue;
|
|
}
|
|
if (char === "'") inSingle = false;
|
|
continue;
|
|
}
|
|
if (inDouble) {
|
|
if (char === '"') inDouble = false;
|
|
continue;
|
|
}
|
|
if (char === "'") {
|
|
inSingle = true;
|
|
continue;
|
|
}
|
|
if (char === '"') {
|
|
inDouble = true;
|
|
continue;
|
|
}
|
|
if (char === "(") {
|
|
depth += 1;
|
|
continue;
|
|
}
|
|
if (char === ")") {
|
|
depth = Math.max(0, depth - 1);
|
|
continue;
|
|
}
|
|
if (char === "," && depth === 0) {
|
|
items.push(current.slice(0, -1).trim());
|
|
current = "";
|
|
}
|
|
}
|
|
if (current.trim()) {
|
|
items.push(current.trim());
|
|
}
|
|
return items;
|
|
}
|
|
|
|
function extractSelectClause(sql) {
|
|
const source = String(sql || "").trim();
|
|
const selectIndex = indexOfKeywordAtTopLevel(source, "SELECT");
|
|
if (selectIndex < 0) return null;
|
|
const fromIndex = indexOfKeywordAtTopLevel(source, "FROM", selectIndex + 6);
|
|
if (fromIndex < 0) return null;
|
|
let clause = source.slice(selectIndex + 6, fromIndex).trim();
|
|
clause = clause.replace(/^DISTINCT\s+/i, "").trim();
|
|
return clause || null;
|
|
}
|
|
|
|
function stripOuterParens(value) {
|
|
let text = String(value || "").trim();
|
|
while (text.startsWith("(") && text.endsWith(")")) {
|
|
let depth = 0;
|
|
let balanced = true;
|
|
for (let index = 0; index < text.length; index += 1) {
|
|
const char = text[index];
|
|
if (char === "(") depth += 1;
|
|
if (char === ")") depth -= 1;
|
|
if (depth === 0 && index < text.length - 1) {
|
|
balanced = false;
|
|
break;
|
|
}
|
|
}
|
|
if (!balanced) break;
|
|
text = text.slice(1, -1).trim();
|
|
}
|
|
return text;
|
|
}
|
|
|
|
function extractExplicitAlias(selectItem) {
|
|
const item = String(selectItem || "").trim();
|
|
const asIndex = indexOfKeywordAtTopLevel(item, "AS");
|
|
if (asIndex < 0) return null;
|
|
const aliasText = item.slice(asIndex + 2).trim();
|
|
const alias = normalizeColumnAlias(aliasText);
|
|
return alias || null;
|
|
}
|
|
|
|
function inferColumnNameFromSelectItem(selectItem, expandedItem) {
|
|
const explicitAlias = extractExplicitAlias(selectItem) || extractExplicitAlias(expandedItem);
|
|
if (explicitAlias) return explicitAlias;
|
|
|
|
const trimmedOriginal = stripOuterParens(String(selectItem || "").trim());
|
|
const placeholderOnly = /^\{\{\s*([^{}]+?)\s*\}\}$/.exec(trimmedOriginal);
|
|
if (placeholderOnly) {
|
|
const described = describeTemplateToken(placeholderOnly[1]);
|
|
if (described.kind === "field") {
|
|
return described.name;
|
|
}
|
|
}
|
|
|
|
const trimmedExpanded = stripOuterParens(String(expandedItem || "").trim());
|
|
const field = findField(trimmedExpanded);
|
|
if (field) {
|
|
return field.alias;
|
|
}
|
|
const quotedIdentifier = /^"([^"]+)"$/.exec(trimmedExpanded);
|
|
if (quotedIdentifier) {
|
|
return quotedIdentifier[1];
|
|
}
|
|
const simpleIdentifier = /^[A-Za-z_][A-Za-z0-9_]*$/.exec(trimmedExpanded);
|
|
if (simpleIdentifier) {
|
|
return simpleIdentifier[0];
|
|
}
|
|
return null;
|
|
}
|
|
|
|
function inferCustomSqlColumns(inputSql, expandedSql) {
|
|
const inputSelect = extractSelectClause(inputSql);
|
|
const expandedSelect = extractSelectClause(expandedSql);
|
|
if (!inputSelect || !expandedSelect) {
|
|
return [];
|
|
}
|
|
const inputItems = splitTopLevelComma(inputSelect);
|
|
const expandedItems = splitTopLevelComma(expandedSelect);
|
|
if (inputItems.length !== expandedItems.length) {
|
|
return [];
|
|
}
|
|
const inferred = inputItems.map((item, index) =>
|
|
inferColumnNameFromSelectItem(item, expandedItems[index])
|
|
);
|
|
return inferred.every(Boolean) ? inferred : [];
|
|
}
|
|
|
|
function toCamelCase(value) {
|
|
const text = String(value || "").trim();
|
|
if (!text) return "";
|
|
const parts = text
|
|
.replace(/([a-z0-9])([A-Z])/g, "$1 $2")
|
|
.split(/[^A-Za-z0-9]+/)
|
|
.filter(Boolean)
|
|
.map((part) => part.toLowerCase());
|
|
if (parts.length === 0) return "";
|
|
return parts[0] + parts.slice(1).map((part) => part[0].toUpperCase() + part.slice(1)).join("");
|
|
}
|
|
|
|
function uniqueBy(items, keyFn) {
|
|
const seen = new Set();
|
|
const result = [];
|
|
for (const item of items) {
|
|
const key = keyFn(item);
|
|
if (seen.has(key)) continue;
|
|
seen.add(key);
|
|
result.push(item);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
function resolveFieldReference(input, fieldCatalog = FIELD_CATALOG) {
|
|
const raw = String(input || "").trim();
|
|
if (!raw) {
|
|
throw new Error("Field reference is required.");
|
|
}
|
|
const placeholderOnly = /^\{\{\s*([^{}]+?)\s*\}\}$/.exec(raw);
|
|
if (placeholderOnly) {
|
|
const described = describeTemplateToken(placeholderOnly[1]);
|
|
if (described.kind !== "field") {
|
|
throw new Error(`"${raw}" resolves to a macro, not a field alias.`);
|
|
}
|
|
return {
|
|
name: described.name,
|
|
expr: described.resolved,
|
|
};
|
|
}
|
|
const field = findField(raw, fieldCatalog);
|
|
if (field) {
|
|
return {
|
|
name: field.alias,
|
|
expr: field.expr,
|
|
};
|
|
}
|
|
const suggestion = suggestItemName(raw, fieldCatalog.map((item) => item.alias));
|
|
throw new Error(
|
|
suggestion
|
|
? `Unknown field reference "${raw}". Did you mean "${suggestion}"?`
|
|
: `Unknown field reference "${raw}". Use --catalog to inspect supported field aliases.`
|
|
);
|
|
}
|
|
|
|
function parseSelectSpec(value, fieldCatalog = FIELD_CATALOG) {
|
|
const rawItems = splitCsv(value, []);
|
|
if (rawItems.length === 0) {
|
|
throw new Error("Pass at least one field in --select when using --query.");
|
|
}
|
|
return rawItems.map((item) => {
|
|
const [sourceText, outputText] = item.split("=", 2).map((part) => part.trim());
|
|
const field = resolveFieldReference(sourceText, fieldCatalog);
|
|
return {
|
|
source: sourceText,
|
|
fieldName: field.name,
|
|
expr: field.expr,
|
|
outputName: outputText || field.name,
|
|
kind: "select",
|
|
};
|
|
});
|
|
}
|
|
|
|
function defaultMeasureAlias(aggregate, fieldName) {
|
|
const base = fieldName === "*" ? "all" : fieldName;
|
|
const prefixMap = {
|
|
sum: "sum",
|
|
avg: "avg",
|
|
min: "min",
|
|
max: "max",
|
|
count: "count",
|
|
"count-distinct": "countDistinct",
|
|
};
|
|
return `${prefixMap[aggregate] || aggregate}${base[0]?.toUpperCase() || ""}${base.slice(1)}`;
|
|
}
|
|
|
|
function parseMeasureSpec(value, fieldCatalog = FIELD_CATALOG) {
|
|
const rawItems = splitCsv(value, []);
|
|
if (rawItems.length === 0) return [];
|
|
const supported = new Set(["sum", "avg", "min", "max", "count", "count-distinct"]);
|
|
return rawItems.map((item) => {
|
|
const parts = item.split(":").map((part) => part.trim());
|
|
if (parts.length < 2 || parts.length > 3) {
|
|
throw new Error(
|
|
`Unsupported --measure item "${item}". Use aggregate:fieldAlias[:outputAlias], for example sum:bookingValue:totalBooking.`
|
|
);
|
|
}
|
|
const [aggregateRaw, fieldRaw, outputRaw] = parts;
|
|
const aggregate = aggregateRaw.toLowerCase();
|
|
if (!supported.has(aggregate)) {
|
|
throw new Error(
|
|
`Unsupported aggregate "${aggregateRaw}". Use one of: ${Array.from(supported).join(", ")}.`
|
|
);
|
|
}
|
|
let fieldName = "*";
|
|
let fieldExpr = "*";
|
|
if (fieldRaw !== "*") {
|
|
const field = resolveFieldReference(fieldRaw, fieldCatalog);
|
|
fieldName = field.name;
|
|
fieldExpr = field.expr;
|
|
} else if (!["count"].includes(aggregate)) {
|
|
throw new Error(`Aggregate "${aggregate}" does not support "*".`);
|
|
}
|
|
let expr = "";
|
|
if (aggregate === "count-distinct") {
|
|
expr = `COUNT(DISTINCT ${fieldExpr})`;
|
|
} else if (aggregate === "count" && fieldExpr === "*") {
|
|
expr = "COUNT(*)";
|
|
} else {
|
|
expr = `${aggregate.toUpperCase()}(${fieldExpr})`;
|
|
}
|
|
return {
|
|
aggregate,
|
|
fieldName,
|
|
expr,
|
|
outputName: outputRaw || defaultMeasureAlias(aggregate, fieldName),
|
|
kind: "measure",
|
|
};
|
|
});
|
|
}
|
|
|
|
function parseGroupBySpec(value, selectItems, fieldCatalog = FIELD_CATALOG) {
|
|
const rawItems = splitCsv(value, []);
|
|
if (rawItems.length === 0) return [];
|
|
return rawItems.map((item) => {
|
|
const raw = String(item || "").trim();
|
|
const selectMatch = selectItems.find((entry) => entry.outputName.toLowerCase() === raw.toLowerCase());
|
|
if (selectMatch) {
|
|
return {
|
|
source: raw,
|
|
outputName: selectMatch.outputName,
|
|
expr: selectMatch.expr,
|
|
};
|
|
}
|
|
const field = resolveFieldReference(raw, fieldCatalog);
|
|
return {
|
|
source: raw,
|
|
outputName: field.name,
|
|
expr: field.expr,
|
|
};
|
|
});
|
|
}
|
|
|
|
function expandKnownNamesInClause(clause, replacements) {
|
|
let result = String(clause || "");
|
|
for (const [name, expr] of replacements) {
|
|
const pattern = new RegExp(`\\b${name.replace(/[.*+?^${}()|[\\]\\\\]/g, "\\$&")}\\b`, "g");
|
|
result = result.replace(pattern, expr);
|
|
}
|
|
return result;
|
|
}
|
|
|
|
function resolveDeclarativeClause(clause, replacements) {
|
|
if (!clause) return null;
|
|
const rendered = expandSqlTemplate(clause);
|
|
const normalized = expandKnownNamesInClause(rendered.sql, replacements).trim();
|
|
return {
|
|
input: clause,
|
|
sql: normalized,
|
|
templateTokens: rendered.templateTokens,
|
|
};
|
|
}
|
|
|
|
function parseOrderBySpec(value, availableOutputs, fieldCatalog = FIELD_CATALOG) {
|
|
const rawItems = splitCsv(value, []);
|
|
if (rawItems.length === 0) return [];
|
|
return rawItems.map((item) => {
|
|
const raw = String(item || "").trim();
|
|
const colonMatch = /^(.+?)(?::(asc|desc))$/i.exec(raw);
|
|
const spaceMatch = /^(.+?)\s+(asc|desc)$/i.exec(raw);
|
|
const source = (colonMatch?.[1] || spaceMatch?.[1] || raw).trim();
|
|
const direction = (colonMatch?.[2] || spaceMatch?.[2] || "asc").toUpperCase();
|
|
const outputMatch = availableOutputs.find((entry) => entry.outputName.toLowerCase() === source.toLowerCase());
|
|
if (outputMatch) {
|
|
return {
|
|
source,
|
|
outputName: outputMatch.outputName,
|
|
sql: `${outputMatch.outputName} ${direction}`,
|
|
};
|
|
}
|
|
const field = resolveFieldReference(source, fieldCatalog);
|
|
return {
|
|
source,
|
|
outputName: field.name,
|
|
sql: `${field.expr} ${direction}`,
|
|
};
|
|
});
|
|
}
|
|
|
|
function sourceNameFromExpression(expr) {
|
|
const text = String(expr || "");
|
|
if (text.includes(SA_ATTACH_OPPORTUNITIES_DATASET)) return SOURCE_OPPORTUNITIES;
|
|
if (text.includes(SA_ATTACH_HOURS_DATASET)) return SOURCE_HOURS;
|
|
if (text.includes(SA_ATTACH_PRESALES_AUX_DATASET)) return SOURCE_PRESALES_AUX;
|
|
if (text.includes(SA_ATTACH_REVENUE_LINES_DATASET)) return SOURCE_REVENUE_LINES;
|
|
if (text.includes('"Service Request"') || text.includes('"SR Team"') || text.includes('"Security"')) {
|
|
return SOURCE_DV_SE_TEAM;
|
|
}
|
|
if (text.includes('"Opportunity"') || text.includes('"Customer"') || text.includes('"Effort"')) {
|
|
return SOURCE_DV_SE_TEAM;
|
|
}
|
|
return null;
|
|
}
|
|
|
|
function resolveQuerySource({ outputRefs, groupByItems, orderByItems, whereClause, havingClause }) {
|
|
const candidates = [
|
|
...outputRefs.map((entry) => entry.expr),
|
|
...groupByItems.map((entry) => entry.expr),
|
|
...orderByItems.map((entry) => entry.sql),
|
|
whereClause?.sql,
|
|
havingClause?.sql,
|
|
]
|
|
.map((expr) => sourceNameFromExpression(expr))
|
|
.filter(Boolean);
|
|
const uniqueSources = Array.from(new Set(candidates));
|
|
if (uniqueSources.length > 1) {
|
|
throw new Error(
|
|
`--query cannot mix fields from multiple backend sources in one logical SQL statement: ${uniqueSources.join(", ")}. Use a built-in intent/view or custom --sql for joined/mixed-source asks.`
|
|
);
|
|
}
|
|
return uniqueSources[0] || SOURCE_DV_SE_TEAM;
|
|
}
|
|
|
|
function normalizeQueryMarkerText(value) {
|
|
return String(value || "").toLowerCase().replace(/[^a-z0-9]+/g, "");
|
|
}
|
|
|
|
function shouldPreferOrgWideQueryFields(args) {
|
|
if (normalizeScopeInput(args.scope || SCOPE_AUTO) === SCOPE_MY_TEAM) {
|
|
return false;
|
|
}
|
|
const queryText = normalizeQueryMarkerText([
|
|
args.select,
|
|
args.measure,
|
|
args["group-by"],
|
|
args.where,
|
|
args.having,
|
|
args["order-by"],
|
|
].filter(Boolean).join(" "));
|
|
for (const marker of QUERY_MY_TEAM_ONLY_ALIAS_MARKERS) {
|
|
if (queryText.includes(normalizeQueryMarkerText(marker))) {
|
|
return false;
|
|
}
|
|
}
|
|
return true;
|
|
}
|
|
|
|
function buildQueryFieldCatalog(args) {
|
|
if (!shouldPreferOrgWideQueryFields(args)) {
|
|
return FIELD_CATALOG;
|
|
}
|
|
return FIELD_CATALOG.map((field) => {
|
|
const override = QUERY_ORG_WIDE_FIELD_OVERRIDES[field.alias];
|
|
return override ? { ...field, ...override } : field;
|
|
});
|
|
}
|
|
|
|
function buildQueryModeSql(args) {
|
|
const queryFieldCatalog = buildQueryFieldCatalog(args);
|
|
const selectItems = parseSelectSpec(args.select || "", queryFieldCatalog);
|
|
const measureItems = parseMeasureSpec(args.measure || "", queryFieldCatalog);
|
|
const explicitGroupBy = parseGroupBySpec(args["group-by"] || "", selectItems, queryFieldCatalog);
|
|
const outputRefs = [...selectItems, ...measureItems];
|
|
const replacementEntries = uniqueBy(
|
|
[
|
|
...queryFieldCatalog.map((field) => [field.alias, field.expr]),
|
|
...macroNames().map((name) => [name, MACRO_CATALOG[name].resolve()]),
|
|
...outputRefs.map((entry) => [entry.outputName, entry.expr]),
|
|
],
|
|
([name]) => name.toLowerCase()
|
|
);
|
|
const whereClause = resolveDeclarativeClause(args.where || "", replacementEntries);
|
|
const havingClause = resolveDeclarativeClause(args.having || "", replacementEntries);
|
|
const orderByItems = parseOrderBySpec(args["order-by"] || "", outputRefs, queryFieldCatalog);
|
|
const distinct = args.distinct === "true";
|
|
const groupByItems =
|
|
measureItems.length > 0
|
|
? uniqueBy([...selectItems, ...explicitGroupBy], (entry) => entry.expr)
|
|
: explicitGroupBy;
|
|
const sourceName = resolveQuerySource({
|
|
outputRefs,
|
|
groupByItems,
|
|
orderByItems,
|
|
whereClause,
|
|
havingClause,
|
|
});
|
|
const sourcePolicy = resolveDatasetPolicy(sourceName);
|
|
const requestedScope = normalizeScopeInput(args.scope || SCOPE_AUTO);
|
|
const effectiveScope = resolveEffectiveScope({ requestedScope, sourceName });
|
|
const sourcePredicates =
|
|
sourceName === SOURCE_DV_SE_TEAM
|
|
? [MACRO_CATALOG.securityEntryPointFilter.resolve()]
|
|
: buildXsaScopePredicates(sourcePolicy?.subjectArea, effectiveScope);
|
|
const wherePredicates = [
|
|
...sourcePredicates,
|
|
...(whereClause?.sql ? [whereClause.sql] : []),
|
|
];
|
|
|
|
const selectSql = [
|
|
...selectItems.map((item) => ` ${item.expr} AS ${item.outputName}`),
|
|
...measureItems.map((item) => ` ${item.expr} AS ${item.outputName}`),
|
|
];
|
|
|
|
const clauses = [
|
|
`SELECT${distinct ? " DISTINCT" : ""}`,
|
|
selectSql.join(",\n"),
|
|
`FROM ${sourcePolicy?.subjectArea || MACRO_CATALOG.subjectArea.resolve()}`,
|
|
];
|
|
if (wherePredicates.length > 0) {
|
|
clauses.push(`WHERE ${wherePredicates.join("\n AND ")}`);
|
|
}
|
|
if (groupByItems.length > 0) {
|
|
clauses.push(`GROUP BY ${groupByItems.map((item) => item.expr).join(", ")}`);
|
|
}
|
|
if (havingClause?.sql) {
|
|
clauses.push(`HAVING ${havingClause.sql}`);
|
|
}
|
|
if (orderByItems.length > 0) {
|
|
clauses.push(`ORDER BY ${orderByItems.map((item) => item.sql).join(", ")}`);
|
|
}
|
|
|
|
return {
|
|
sql: clauses.join("\n"),
|
|
columns: outputRefs.map((entry) => entry.outputName),
|
|
querySpec: {
|
|
select: selectItems.map(({ source, fieldName, outputName }) => ({ source, fieldName, outputName })),
|
|
measure: measureItems.map(({ aggregate, fieldName, outputName }) => ({ aggregate, fieldName, outputName })),
|
|
where: whereClause ? { input: whereClause.input, sql: whereClause.sql } : null,
|
|
groupBy: groupByItems.map(({ outputName, expr }) => ({ outputName, expr })),
|
|
having: havingClause ? { input: havingClause.input, sql: havingClause.sql } : null,
|
|
orderBy: orderByItems.map(({ source, sql }) => ({ source, sql })),
|
|
distinct,
|
|
source: sourceName,
|
|
requestedScope,
|
|
effectiveScope,
|
|
},
|
|
templateTokens: [...(whereClause?.templateTokens || []), ...(havingClause?.templateTokens || [])],
|
|
};
|
|
}
|
|
|
|
function buildSrResourcePredicate(resourceEmailSql, matchRole) {
|
|
if (matchRole === "lead") {
|
|
return `"Service Request"."Lead Email" = ${resourceEmailSql}`;
|
|
}
|
|
return `(
|
|
"SR Team"."SE Team Level 1 Email Address" = ${resourceEmailSql}
|
|
OR "SR Team"."SE Team Level 2 Email Address" = ${resourceEmailSql}
|
|
OR "SR Team"."SE Team Level 3 Email Address" = ${resourceEmailSql}
|
|
OR "SR Team"."SE Team Level 4 Email Address" = ${resourceEmailSql}
|
|
)`;
|
|
}
|
|
|
|
function renderViewSql(viewName, options = {}) {
|
|
const definition = VIEW_DEFS[viewName];
|
|
if (!definition) {
|
|
throw new Error(`Unknown view "${viewName}".`);
|
|
}
|
|
const requestedScope = normalizeScopeInput(options.scope || options.requestedScope || SCOPE_AUTO);
|
|
const effectiveScope = resolveEffectiveScope({
|
|
requestedScope,
|
|
viewName,
|
|
definition,
|
|
});
|
|
return definition.buildSql({ ...options, scope: effectiveScope });
|
|
}
|
|
|
|
function denseExpr(expr, kind) {
|
|
if (kind === "text") {
|
|
return `IFNULL(${expr}, '${NULL_SENTINEL}')`;
|
|
}
|
|
return `IFNULL(CAST(${expr} AS VARCHAR(${VARCHAR_SIZE})), '${NULL_SENTINEL}')`;
|
|
}
|
|
|
|
function denseSelect(columns) {
|
|
return columns
|
|
.map((column, index) => ` ${denseExpr(column.expr, column.kind)} AS c${index}`)
|
|
.join(",\n");
|
|
}
|
|
|
|
function plainSelect(columns) {
|
|
return columns.map((column) => ` ${column.expr} AS ${column.name}`).join(",\n");
|
|
}
|
|
|
|
function buildDenseQuery({ columns, fromClause, whereClauses = [], groupBy = null, orderBy = null, distinct = false }) {
|
|
const clauses = [`SELECT${distinct ? " DISTINCT" : ""}`, denseSelect(columns), fromClause];
|
|
if (whereClauses.length > 0) {
|
|
clauses.push(`WHERE ${whereClauses.join("\n AND ")}`);
|
|
}
|
|
if (groupBy) {
|
|
clauses.push(`GROUP BY ${groupBy}`);
|
|
}
|
|
if (orderBy) {
|
|
clauses.push(`ORDER BY ${orderBy}`);
|
|
}
|
|
return clauses.join("\n");
|
|
}
|
|
|
|
function buildSelectQuery({ columns, fromClause, whereClauses = [], groupBy = null, orderBy = null, distinct = false }) {
|
|
const clauses = [`SELECT${distinct ? " DISTINCT" : ""}`, plainSelect(columns), fromClause];
|
|
if (whereClauses.length > 0) {
|
|
clauses.push(`WHERE ${whereClauses.join("\n AND ")}`);
|
|
}
|
|
if (groupBy) {
|
|
clauses.push(`GROUP BY ${groupBy}`);
|
|
}
|
|
if (orderBy) {
|
|
clauses.push(`ORDER BY ${orderBy}`);
|
|
}
|
|
return clauses.join("\n");
|
|
}
|
|
|
|
function xsaColumn(dataset, columnName) {
|
|
return `${dataset}."Columns"."${columnName}"`;
|
|
}
|
|
|
|
function buildXsaTeamFilter(dataset, maxLevel = 8) {
|
|
const prefix = `${dataset}."Columns"`;
|
|
const levels = Array.from({ length: maxLevel }, (_, index) => index + 1)
|
|
.map(
|
|
(level) =>
|
|
`${prefix}."SE Team Level ${level} Email Address" IN (VALUEOF(NQ_SESSION."SEC_ARIA_SI-SR-TEAM"))`
|
|
)
|
|
.join("\n OR ");
|
|
return `(\n ${levels}\n )`;
|
|
}
|
|
|
|
function buildXsaEmailMatchPredicate(dataset, emailSql, minLevel = 1, maxLevel = 8) {
|
|
const prefix = `${dataset}."Columns"`;
|
|
const levels = Array.from({ length: maxLevel - minLevel + 1 }, (_, index) => minLevel + index)
|
|
.map((level) => `${prefix}."SE Team Level ${level} Email Address" = ${emailSql}`)
|
|
.join("\n OR ");
|
|
return `(\n ${levels}\n )`;
|
|
}
|
|
|
|
function buildCoverageSqlForOpportunityIds(opportunityIds, coveragePredicate) {
|
|
if (!Array.isArray(opportunityIds) || opportunityIds.length === 0) {
|
|
throw new Error("buildCoverageSqlForOpportunityIds requires at least one opportunity id.");
|
|
}
|
|
return buildDenseQuery({
|
|
columns: SA_ATTACH_PRESALES_AUX_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_PRESALES_AUX_DATASET}`,
|
|
whereClauses: [
|
|
coveragePredicate,
|
|
`${xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "Opportunity ID")} IN (${toSqlList(opportunityIds)})`,
|
|
],
|
|
orderBy: `${xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "Opportunity ID")}`,
|
|
});
|
|
}
|
|
|
|
function buildCurrentTeamCoverageSqlForOpportunityIds(opportunityIds) {
|
|
return buildCoverageSqlForOpportunityIds(opportunityIds, buildXsaTeamFilter(SA_ATTACH_PRESALES_AUX_DATASET));
|
|
}
|
|
|
|
function buildExplicitCoverageSqlForOpportunityIds(opportunityIds, emailSql) {
|
|
return buildCoverageSqlForOpportunityIds(
|
|
opportunityIds,
|
|
buildXsaEmailMatchPredicate(SA_ATTACH_PRESALES_AUX_DATASET, emailSql)
|
|
);
|
|
}
|
|
|
|
function buildManagerResourceCoverageSqlForOpportunityIds(opportunityIds, managerEmails) {
|
|
if (!Array.isArray(opportunityIds) || opportunityIds.length === 0) {
|
|
throw new Error("buildManagerResourceCoverageSqlForOpportunityIds requires at least one opportunity id.");
|
|
}
|
|
if (!Array.isArray(managerEmails) || managerEmails.length === 0) {
|
|
throw new Error("buildManagerResourceCoverageSqlForOpportunityIds requires at least one manager email.");
|
|
}
|
|
return buildDenseQuery({
|
|
columns: SA_ATTACH_PRESALES_AUX_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_PRESALES_AUX_DATASET}`,
|
|
whereClauses: [
|
|
`${xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 4 Email Address")} IN (${toSqlList(managerEmails)})`,
|
|
`${xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "SE Team Level 5 Email Address")} IS NOT NULL`,
|
|
`${xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "Opportunity ID")} IN (${toSqlList(opportunityIds)})`,
|
|
],
|
|
orderBy: `${xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "Opportunity ID")}, ${xsaColumn(
|
|
SA_ATTACH_PRESALES_AUX_DATASET,
|
|
"SE Team Level 5 Email Address"
|
|
)}`,
|
|
});
|
|
}
|
|
|
|
function buildWorkloadCloseWindowSql({
|
|
fromDateSql,
|
|
toDateSql,
|
|
cluster = SA_ATTACH_DEFAULT_CLUSTER,
|
|
ownerEmail = null,
|
|
ownerManagerEmail = null,
|
|
forecastTypes = [],
|
|
}) {
|
|
return buildDenseQuery({
|
|
columns: WORKLOAD_MANAGER_RESOURCE_PIPELINE_COLUMNS,
|
|
fromClause: `FROM ${SA_ATTACH_OPPORTUNITIES_DATASET}`,
|
|
whereClauses: buildSaAttachOpportunityWhereClauses({
|
|
dataset: SA_ATTACH_OPPORTUNITIES_DATASET,
|
|
fromDateSql,
|
|
toDateSql,
|
|
fiscalYear: null,
|
|
previousFiscalYear: null,
|
|
cluster,
|
|
solutions: ["Cloud Infrastructure - Workloads"],
|
|
statuses: SA_ATTACH_DEFAULT_ALL_STATUSES,
|
|
productLobs: ["Cloud Infrastructure"],
|
|
revenueTypeGroups: ["WORKLOAD"],
|
|
salesCreditType: SA_ATTACH_DEFAULT_SALES_CREDIT_TYPE,
|
|
minOpportunityProbability: SA_ATTACH_DEFAULT_MIN_OPPORTUNITY_PROBABILITY,
|
|
ownerEmail,
|
|
ownerManagerEmail,
|
|
forecastTypes,
|
|
}),
|
|
orderBy: `${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Date")}, ${xsaColumn(
|
|
SA_ATTACH_OPPORTUNITIES_DATASET,
|
|
"Revenue Line ID"
|
|
)}`,
|
|
});
|
|
}
|
|
|
|
function buildManagerResourceWorkloadSql({ fromDateSql, toDateSql, cluster = SA_ATTACH_DEFAULT_CLUSTER }) {
|
|
return buildWorkloadCloseWindowSql({ fromDateSql, toDateSql, cluster });
|
|
}
|
|
|
|
function buildRevenueTeamFilter() {
|
|
return buildXsaTeamFilter(SA_ATTACH_REVENUE_LINES_DATASET);
|
|
}
|
|
|
|
function buildSaAttachRevenueLineCloseYearPredicate(dataset, statuses, fiscalYear, previousFiscalYear) {
|
|
const statusExpr = xsaColumn(dataset, "Revenue Line Status");
|
|
const closeYearExpr = xsaColumn(dataset, "Revenue Line Close Year");
|
|
const openIncluded = statuses.includes("Open");
|
|
const closedStatuses = statuses.filter((status) => status !== "Open");
|
|
const clauses = [];
|
|
|
|
if (openIncluded) {
|
|
clauses.push(`${statusExpr} = 'Open'`);
|
|
}
|
|
|
|
if (closedStatuses.length > 0) {
|
|
clauses.push(`(
|
|
${statusExpr} IN (${toSqlList(closedStatuses)})
|
|
AND ${closeYearExpr} IN (${toSqlList([previousFiscalYear, fiscalYear].filter(Boolean))})
|
|
)`);
|
|
}
|
|
|
|
if (clauses.length === 0) {
|
|
return "1 = 1";
|
|
}
|
|
|
|
return clauses.length === 1 ? clauses[0] : `(\n ${clauses.join("\n OR ")}\n )`;
|
|
}
|
|
|
|
function buildSaAttachOpportunityWhereClauses({
|
|
dataset,
|
|
fiscalYear,
|
|
previousFiscalYear,
|
|
fromDateSql,
|
|
toDateSql,
|
|
cluster,
|
|
solutions,
|
|
statuses,
|
|
productLobs,
|
|
revenueTypeGroups,
|
|
salesCreditType,
|
|
minOpportunityProbability,
|
|
ownerEmail,
|
|
ownerManagerEmail,
|
|
forecastTypes = [],
|
|
}) {
|
|
const hasExplicitCloseDateWindow = Boolean(fromDateSql || toDateSql);
|
|
return [
|
|
`${xsaColumn(dataset, "Cluster")} = ${quoteLiteral(cluster)}`,
|
|
`${xsaColumn(dataset, "Solution")} IN (${toSqlList(solutions)})`,
|
|
`${xsaColumn(dataset, "Revenue Line Status")} IN (${toSqlList(statuses)})`,
|
|
...(!hasExplicitCloseDateWindow && (fiscalYear || previousFiscalYear)
|
|
? [buildSaAttachRevenueLineCloseYearPredicate(dataset, statuses, fiscalYear, previousFiscalYear)]
|
|
: []),
|
|
...(fromDateSql ? [`${xsaColumn(dataset, "Revenue Line Close Date")} >= ${fromDateSql}`] : []),
|
|
...(toDateSql ? [`${xsaColumn(dataset, "Revenue Line Close Date")} <= ${toDateSql}`] : []),
|
|
`${xsaColumn(dataset, "Executive Product Lob")} IN (${toSqlList(productLobs)})`,
|
|
`${xsaColumn(dataset, "Revenue Type Group")} IN (${toSqlList(revenueTypeGroups)})`,
|
|
`${xsaColumn(dataset, "Sales Credit Type")} = ${quoteLiteral(salesCreditType)}`,
|
|
`${xsaColumn(dataset, "Opportunity Probability")} >= ${Number(minOpportunityProbability)}`,
|
|
...(forecastTypes.length > 0
|
|
? [`${xsaColumn(dataset, "Revenue Line Forecast Type Group")} IN (${toSqlList(forecastTypes)})`]
|
|
: []),
|
|
...(ownerEmail
|
|
? [`${xsaColumn(dataset, "Level 7 Territory Owner E-mail")} = ${quoteLiteral(ownerEmail)}`]
|
|
: []),
|
|
...(ownerManagerEmail
|
|
? [
|
|
`(
|
|
${xsaColumn(dataset, "Level 2 Territory Owner E-mail")} = ${quoteLiteral(ownerManagerEmail)}
|
|
OR ${xsaColumn(dataset, "Level 3 Territory Owner E-mail")} = ${quoteLiteral(ownerManagerEmail)}
|
|
OR ${xsaColumn(dataset, "Level 4 Territory Owner E-mail")} = ${quoteLiteral(ownerManagerEmail)}
|
|
OR ${xsaColumn(dataset, "Level 5 Territory Owner E-mail")} = ${quoteLiteral(ownerManagerEmail)}
|
|
OR ${xsaColumn(dataset, "Level 6 Territory Owner E-mail")} = ${quoteLiteral(ownerManagerEmail)}
|
|
)`,
|
|
]
|
|
: []),
|
|
];
|
|
}
|
|
|
|
function buildPrimaryOpportunityWhereClauses({
|
|
statuses = [],
|
|
fromDateSql = null,
|
|
toDateSql = null,
|
|
fiscalYear = null,
|
|
cluster = null,
|
|
ownerEmail = null,
|
|
ownerManagerEmail = null,
|
|
}) {
|
|
const clauses = [];
|
|
const closeDateExpr = xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Revenue Line Close Date");
|
|
const normalizedCluster = String(cluster || "").trim();
|
|
if (normalizedCluster) {
|
|
clauses.push(`${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Cluster")} = ${quoteLiteral(normalizedCluster)}`);
|
|
}
|
|
if (fromDateSql) {
|
|
clauses.push(`${closeDateExpr} >= ${fromDateSql}`);
|
|
}
|
|
if (toDateSql) {
|
|
clauses.push(`${closeDateExpr} <= ${toDateSql}`);
|
|
}
|
|
if (!fromDateSql && !toDateSql && fiscalYear) {
|
|
const window = fiscalYearToCloseDateWindow(fiscalYear);
|
|
if (window) {
|
|
clauses.push(`${closeDateExpr} >= DATE ${quoteLiteral(window.fromDate)}`);
|
|
clauses.push(`${closeDateExpr} <= DATE ${quoteLiteral(window.toDate)}`);
|
|
}
|
|
}
|
|
if (statuses.length > 0) {
|
|
clauses.push(`${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Opportunity Status")} IN (${toSqlList(statuses)})`);
|
|
}
|
|
const normalizedOwnerEmail = String(ownerEmail || "").trim().toLowerCase();
|
|
if (normalizedOwnerEmail) {
|
|
clauses.push(`${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 7 Territory Owner E-mail")} = ${quoteLiteral(normalizedOwnerEmail)}`);
|
|
}
|
|
const normalizedOwnerManagerEmail = String(ownerManagerEmail || "").trim().toLowerCase();
|
|
if (normalizedOwnerManagerEmail) {
|
|
clauses.push(`(
|
|
${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 2 Territory Owner E-mail")} = ${quoteLiteral(normalizedOwnerManagerEmail)}
|
|
OR ${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 3 Territory Owner E-mail")} = ${quoteLiteral(normalizedOwnerManagerEmail)}
|
|
OR ${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 4 Territory Owner E-mail")} = ${quoteLiteral(normalizedOwnerManagerEmail)}
|
|
OR ${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 5 Territory Owner E-mail")} = ${quoteLiteral(normalizedOwnerManagerEmail)}
|
|
OR ${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Level 6 Territory Owner E-mail")} = ${quoteLiteral(normalizedOwnerManagerEmail)}
|
|
)`);
|
|
}
|
|
return clauses;
|
|
}
|
|
|
|
function buildSaAttachInvolvementBucketExpr() {
|
|
const myTeamHoursExpr = `SUM(CASE WHEN ${buildXsaTeamFilter(
|
|
SA_ATTACH_PRESALES_AUX_DATASET
|
|
)} THEN ${xsaColumn(
|
|
SA_ATTACH_PRESALES_AUX_DATASET,
|
|
"Opportunity Hours Attribute"
|
|
)} END BY ${xsaColumn(SA_ATTACH_PRESALES_AUX_DATASET, "Opportunity ID")})`;
|
|
return `CASE
|
|
WHEN ${myTeamHoursExpr} > 0 THEN '1. My Team Involvement'
|
|
WHEN ${xsaColumn(SA_ATTACH_OPPORTUNITIES_DATASET, "Presales Involvement Flag")} = 'Y' THEN '2. Other Presales Involvement'
|
|
ELSE '3. No Presales Involvement'
|
|
END`;
|
|
}
|
|
|
|
async function restoreOracleDvSession({ state, meta, baseUrl, statePath = null, metaPath = null }) {
|
|
const targetUrl = meta.targetUrl || meta.referer || DEFAULT_WORKBOOK_URL || new URL("/", baseUrl).toString();
|
|
const pageResponse = await fetch(targetUrl, {
|
|
headers: {
|
|
accept: "text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8",
|
|
cookie: cookieHeaderFor(targetUrl, state.cookies),
|
|
},
|
|
redirect: "manual",
|
|
});
|
|
const pageText = await pageResponse.text();
|
|
let changed = mergeCookiesFromHeader(state, targetUrl, pageResponse.headers.get("set-cookie"));
|
|
|
|
if (pageResponse.status >= 300 && pageResponse.status < 400) {
|
|
throw new Error("Request redirected while restoring Oracle DV session. Refresh auth state first.");
|
|
}
|
|
if (pageResponse.url.includes("signon.oracle.com") || pageResponse.url.includes("signon-int.oracle.com")) {
|
|
throw new Error("Oracle DV session restore redirected to Oracle sign-in. Refresh auth state first.");
|
|
}
|
|
|
|
const clientBin = extractClientBin(pageText, pageResponse.headers) || meta.xBitechClientbin;
|
|
const sessionInfoUrl = new URL(SESSION_INFO_ENDPOINT, baseUrl).toString();
|
|
const sessionResponse = await fetch(sessionInfoUrl, {
|
|
headers: {
|
|
accept: "application/json, text/plain, */*",
|
|
authorization: "session",
|
|
"x-requested-with": "XMLHttpRequest",
|
|
referer: targetUrl,
|
|
...(clientBin ? { "x-bitech-clientbin": clientBin } : {}),
|
|
cookie: cookieHeaderFor(sessionInfoUrl, state.cookies),
|
|
},
|
|
redirect: "manual",
|
|
});
|
|
const sessionText = await sessionResponse.text();
|
|
changed = mergeCookiesFromHeader(state, sessionInfoUrl, sessionResponse.headers.get("set-cookie")) || changed;
|
|
|
|
if (!sessionResponse.ok) {
|
|
throw new Error(`Could not refresh Oracle DV session metadata (${sessionResponse.status}).`);
|
|
}
|
|
|
|
let sessionJson;
|
|
try {
|
|
sessionJson = JSON.parse(sessionText);
|
|
} catch (error) {
|
|
throw new Error(`Could not parse refreshed Oracle DV session metadata: ${error.message}`);
|
|
}
|
|
|
|
const csrfToken = extractCsrfToken(sessionJson);
|
|
if (!csrfToken) {
|
|
throw new Error("Could not extract a refreshed Oracle DV CSRF token.");
|
|
}
|
|
|
|
meta.xCsrfToken = csrfToken;
|
|
if (clientBin) meta.xBitechClientbin = clientBin;
|
|
meta.referer ||= targetUrl;
|
|
meta.targetUrl ||= targetUrl;
|
|
if (statePath && metaPath) {
|
|
await persistSessionArtifacts(statePath, metaPath, state, meta, { ensureParentDir });
|
|
} else if (changed) {
|
|
state.cookies = state.cookies.map((cookie) => normalizeCookie(cookie) || cookie);
|
|
}
|
|
}
|
|
|
|
async function requestJson({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
body,
|
|
statePath = null,
|
|
metaPath = null,
|
|
autoAuthRefresh = true,
|
|
}) {
|
|
const url = new URL(SQL_ENDPOINT, baseUrl);
|
|
const attempts = [1, 2, 3];
|
|
let lastError = null;
|
|
let restoredSession = false;
|
|
let autoRefreshed = false;
|
|
for (const attempt of attempts) {
|
|
try {
|
|
const response = await fetch(url, {
|
|
method: "POST",
|
|
headers: {
|
|
accept: "*/*",
|
|
"content-type": "application/json; charset=UTF-8",
|
|
authorization: "session",
|
|
"x-csrf-token": meta.xCsrfToken,
|
|
"x-bitech-clientbin": meta.xBitechClientbin,
|
|
"x-requested-with": "XMLHttpRequest",
|
|
referer: meta.referer || meta.targetUrl,
|
|
cookie: cookieHeaderFor(url.toString(), state.cookies),
|
|
},
|
|
body: JSON.stringify(body),
|
|
redirect: "manual",
|
|
});
|
|
|
|
const text = await response.text();
|
|
let cookiesChanged = mergeCookiesFromHeader(state, url.toString(), response.headers.get("set-cookie"));
|
|
const contentType = response.headers.get("content-type") || "";
|
|
|
|
if (response.status >= 300 && response.status < 400) {
|
|
throw new Error("Request redirected. Refresh auth state first.");
|
|
}
|
|
if (response.url.includes("signon.oracle.com") || response.url.includes("signon-int.oracle.com")) {
|
|
throw new Error("Session redirected to Oracle sign-in. Refresh auth state first.");
|
|
}
|
|
if ([401, 403].includes(response.status)) {
|
|
if (!restoredSession) {
|
|
await restoreOracleDvSession({ state, meta, baseUrl, statePath, metaPath });
|
|
restoredSession = true;
|
|
continue;
|
|
}
|
|
throw new Error(
|
|
`Request failed (${response.status}) ${url.pathname}: Oracle DV session is not valid anymore. Refresh auth state with refresh-auth-direct.mjs and retry.`
|
|
);
|
|
}
|
|
if (!response.ok) {
|
|
if ([408, 425, 429, 500, 502, 503, 504].includes(response.status) && attempt < attempts.length) {
|
|
await new Promise((resolve) => setTimeout(resolve, 400 * attempt));
|
|
continue;
|
|
}
|
|
throw new Error(`Request failed (${response.status}) ${url.pathname}: ${text.slice(0, 800)}`);
|
|
}
|
|
if (!contentType.includes("json")) {
|
|
throw new Error(`Expected JSON from ${url.pathname}, got ${contentType || "unknown content type"}.`);
|
|
}
|
|
|
|
const payload = JSON.parse(text);
|
|
if (payload?.retryRequestAfterSessionRestore) {
|
|
if (restoredSession) {
|
|
throw new Error("Oracle DV requested a session restore twice. Refresh auth state first.");
|
|
}
|
|
await restoreOracleDvSession({ state, meta, baseUrl, statePath, metaPath });
|
|
restoredSession = true;
|
|
continue;
|
|
}
|
|
|
|
if (cookiesChanged && statePath && metaPath) {
|
|
await persistSessionArtifacts(statePath, metaPath, state, meta, { ensureParentDir });
|
|
}
|
|
|
|
return payload;
|
|
} catch (error) {
|
|
lastError = error;
|
|
if (autoAuthRefresh && !autoRefreshed && isAuthRefreshRequiredError(error)) {
|
|
await runInteractiveAuthRefresh({
|
|
statePath,
|
|
metaPath,
|
|
targetUrl: meta?.targetUrl || meta?.referer || DEFAULT_WORKBOOK_URL,
|
|
});
|
|
await reloadSessionArtifacts({ statePath, metaPath, state, meta });
|
|
autoRefreshed = true;
|
|
restoredSession = false;
|
|
continue;
|
|
}
|
|
if (attempt >= attempts.length) {
|
|
break;
|
|
}
|
|
if (/Refresh auth state first|Expected JSON/.test(error.message)) {
|
|
break;
|
|
}
|
|
await new Promise((resolve) => setTimeout(resolve, 400 * attempt));
|
|
}
|
|
}
|
|
throw lastError;
|
|
}
|
|
|
|
async function executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
queries,
|
|
useCache = true,
|
|
statePath = null,
|
|
metaPath = null,
|
|
autoAuthRefresh = true,
|
|
}) {
|
|
const body = {
|
|
aQueryInfos: queries.map((query) => ({
|
|
sQuery: query.sql,
|
|
nResultsLimit: query.limit,
|
|
})),
|
|
bUseCache: useCache,
|
|
};
|
|
const payload = await requestJson({ state, meta, baseUrl, body, statePath, metaPath, autoAuthRefresh });
|
|
if (!Array.isArray(payload.aQueriesResults)) {
|
|
throw new Error("Oracle DV SQL response did not include aQueriesResults.");
|
|
}
|
|
const remainingResults = [...payload.aQueriesResults];
|
|
return queries.map((query, index) => {
|
|
const exactMatchIndex = remainingResults.findIndex((result) => result?.sQuery === query.sql);
|
|
const matchIndex = exactMatchIndex >= 0 ? exactMatchIndex : 0;
|
|
const matchedResult = remainingResults.splice(matchIndex, 1)[0] || null;
|
|
if (!matchedResult) {
|
|
throw new Error(
|
|
`Oracle DV SQL response did not include result payload for query index ${index}.`
|
|
);
|
|
}
|
|
const status = String(matchedResult?.eStatus || "").toUpperCase();
|
|
if (status && status !== "FINISHED") {
|
|
const backendMessage = matchedResult?.sErrorMessage
|
|
? String(matchedResult.sErrorMessage)
|
|
: `Unexpected query status: ${matchedResult?.eStatus}`;
|
|
throw new Error(`Oracle DV query failed: ${backendMessage}`);
|
|
}
|
|
return matchedResult;
|
|
});
|
|
}
|
|
|
|
async function executeQueryWithFallback({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
primarySql,
|
|
fallbackSql = null,
|
|
limit,
|
|
primarySource = null,
|
|
fallbackSource = null,
|
|
}) {
|
|
try {
|
|
const [result] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
queries: [{ sql: primarySql, limit }],
|
|
});
|
|
return {
|
|
result,
|
|
sql: primarySql,
|
|
source: primarySource,
|
|
fallbackUsed: false,
|
|
apiQueries: [
|
|
{
|
|
label: "primary",
|
|
result,
|
|
source: primarySource,
|
|
},
|
|
],
|
|
warnings: [],
|
|
primaryError: null,
|
|
};
|
|
} catch (primaryError) {
|
|
if (!fallbackSql) {
|
|
throw primaryError;
|
|
}
|
|
const [fallbackResult] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
queries: [{ sql: fallbackSql, limit }],
|
|
});
|
|
return {
|
|
result: fallbackResult,
|
|
sql: fallbackSql,
|
|
source: fallbackSource,
|
|
fallbackUsed: true,
|
|
apiQueries: [
|
|
{
|
|
label: "fallback",
|
|
result: fallbackResult,
|
|
source: fallbackSource,
|
|
},
|
|
],
|
|
warnings: [
|
|
`Primary source ${primarySource || "the configured primary dataset"} failed and the skill fell back to ${fallbackSource || "the fallback dataset"}: ${primaryError.message}`,
|
|
],
|
|
primaryError: primaryError.message,
|
|
};
|
|
}
|
|
}
|
|
|
|
function allowFallbackForRequest(definition, { intent = null, view = null } = {}) {
|
|
if (!definition?.buildFallbackSql) return false;
|
|
if (!definition.fallbackScope || definition.fallbackScope === "always") {
|
|
return true;
|
|
}
|
|
if (definition.fallbackScope === "my-team-only") {
|
|
const intentsAllowedWhenPrimaryFails = new Set([
|
|
"opportunities-current-team-close-window",
|
|
]);
|
|
if (intent && intentsAllowedWhenPrimaryFails.has(intent)) {
|
|
return true;
|
|
}
|
|
return false;
|
|
}
|
|
return false;
|
|
}
|
|
|
|
function defaultBaseLabelForView(name) {
|
|
if (
|
|
[
|
|
"opportunity-details",
|
|
"revenue-line-details",
|
|
"sa-attach-pipeline-details",
|
|
"sa-attach-open-pipeline",
|
|
"sa-attach-won-pipeline",
|
|
"sa-attach-involvement-distribution",
|
|
"sa-attach-hours-by-opportunity",
|
|
"sa-attach-presales-coverage",
|
|
"won-and-open-by-se-opty",
|
|
"opportunities-close-window",
|
|
].includes(name)
|
|
) {
|
|
return "Opportunities/workloads";
|
|
}
|
|
if (["sr-details", "srs-by-resource-email"].includes(name)) {
|
|
return "SRs";
|
|
}
|
|
return "Records";
|
|
}
|
|
|
|
function inferResolvedScopeMode(
|
|
name,
|
|
definition,
|
|
{ source = null, fallbackUsed = false, requestedScope = SCOPE_AUTO, effectiveScope = null } = {}
|
|
) {
|
|
return effectiveScope || resolveEffectiveScope({
|
|
requestedScope,
|
|
sourceName: sourceNameForView(name, definition, { source, fallbackUsed }),
|
|
viewName: name,
|
|
definition,
|
|
fallbackUsed,
|
|
});
|
|
}
|
|
|
|
function buildScopeDescriptor({
|
|
scopeMode = null,
|
|
baseScope = SCOPE_UNKNOWN,
|
|
baseLabel = "Records",
|
|
baseSource = null,
|
|
enrichmentScope = null,
|
|
enrichmentLabel = null,
|
|
enrichmentSource = null,
|
|
fallbackUsed = false,
|
|
fallbackSource = null,
|
|
fallbackScope = null,
|
|
preserveUnmatchedRows = false,
|
|
customDisclosureLines = null,
|
|
components = [],
|
|
requestedScope = SCOPE_AUTO,
|
|
}) {
|
|
const normalizedScopeMode =
|
|
scopeMode ||
|
|
(baseScope === SCOPE_ORG_WIDE && enrichmentScope && enrichmentScope !== baseScope
|
|
? SCOPE_MIXED
|
|
: baseScope);
|
|
const disclosureLines = Array.isArray(customDisclosureLines) && customDisclosureLines.length > 0
|
|
? [...customDisclosureLines]
|
|
: [];
|
|
|
|
if (disclosureLines.length === 0 && normalizedScopeMode === SCOPE_MIXED) {
|
|
disclosureLines.push(`${baseLabel}: org-wide.`);
|
|
if (enrichmentScope === SCOPE_ORG_WIDE) {
|
|
disclosureLines.push(`${enrichmentLabel || "Resources"}: org-wide.`);
|
|
} else {
|
|
disclosureLines.push(`${enrichmentLabel || "Resources"}: only your current team.`);
|
|
}
|
|
if (preserveUnmatchedRows) {
|
|
disclosureLines.push("Rows without a resource match stay in the result with empty resource fields.");
|
|
}
|
|
} else if (disclosureLines.length === 0 && normalizedScopeMode === SCOPE_ORG_WIDE) {
|
|
disclosureLines.push(`${baseLabel}: org-wide.`);
|
|
if (enrichmentScope === SCOPE_ORG_WIDE && enrichmentLabel) {
|
|
disclosureLines.push(`${enrichmentLabel}: org-wide.`);
|
|
}
|
|
if (preserveUnmatchedRows) {
|
|
disclosureLines.push("Rows without a resource match stay in the result with empty resource fields.");
|
|
}
|
|
} else if (disclosureLines.length === 0 && normalizedScopeMode === SCOPE_MY_TEAM) {
|
|
disclosureLines.push(`${baseLabel}: only your current team.`);
|
|
if (enrichmentScope) {
|
|
disclosureLines.push(`${enrichmentLabel || "Resources"}: only your current team.`);
|
|
}
|
|
} else if (disclosureLines.length === 0) {
|
|
disclosureLines.push(`${baseLabel}: scope could not be inferred automatically.`);
|
|
}
|
|
|
|
if (fallbackUsed && fallbackSource) {
|
|
disclosureLines.push(`Fallback used: ${fallbackSource}.`);
|
|
}
|
|
|
|
return {
|
|
scopeMode: normalizedScopeMode,
|
|
baseScope,
|
|
baseLabel,
|
|
baseSource,
|
|
enrichmentScope,
|
|
enrichmentLabel,
|
|
enrichmentSource,
|
|
components,
|
|
requestedScope,
|
|
fallbackUsed: Boolean(fallbackUsed),
|
|
fallbackSource: fallbackUsed ? fallbackSource : null,
|
|
fallbackScope,
|
|
preserveUnmatchedRows: Boolean(preserveUnmatchedRows),
|
|
disclosureLines,
|
|
explanation: disclosureLines.join(" "),
|
|
};
|
|
}
|
|
|
|
function buildViewScopeDescriptor(name, definition = {}, options = {}) {
|
|
const requestedScope = normalizeScopeInput(options.requestedScope || SCOPE_AUTO);
|
|
const sourceName = sourceNameForView(name, definition, options);
|
|
const baseScope = inferResolvedScopeMode(name, definition, {
|
|
...options,
|
|
requestedScope,
|
|
source: sourceName,
|
|
});
|
|
return buildScopeDescriptor({
|
|
scopeMode: baseScope,
|
|
baseScope,
|
|
baseLabel: defaultBaseLabelForView(name),
|
|
baseSource: sourceName,
|
|
fallbackUsed: options.fallbackUsed || false,
|
|
fallbackSource: definition?.fallbackSource || null,
|
|
fallbackScope: definition?.fallbackScope || null,
|
|
requestedScope,
|
|
components: [
|
|
buildScopeComponent({
|
|
name: "records",
|
|
label: defaultBaseLabelForView(name),
|
|
source: sourceName,
|
|
scope: baseScope,
|
|
}),
|
|
],
|
|
});
|
|
}
|
|
|
|
function buildIntentScopeDescriptor(name, definition) {
|
|
if (name === "workloads-by-manager-resource-close-window") {
|
|
return buildScopeDescriptor({
|
|
scopeMode: SCOPE_ORG_WIDE,
|
|
baseScope: SCOPE_ORG_WIDE,
|
|
baseLabel: "Opportunities/workloads",
|
|
baseSource: SOURCE_OPPORTUNITIES,
|
|
enrichmentScope: SCOPE_ORG_WIDE,
|
|
enrichmentLabel: "Manager/resource allocation",
|
|
enrichmentSource: SOURCE_PRESALES_AUX,
|
|
customDisclosureLines: [
|
|
"Opportunities/workloads: org-wide.",
|
|
"Manager/resource allocation: org-wide from Knowledge DV - Presales Involvement Aux - V1.",
|
|
],
|
|
components: [
|
|
buildScopeComponent({
|
|
name: "commercialRows",
|
|
label: "Opportunities/workloads",
|
|
source: SOURCE_OPPORTUNITIES,
|
|
scope: SCOPE_ORG_WIDE,
|
|
}),
|
|
buildScopeComponent({
|
|
name: "managerResourceAllocation",
|
|
label: "Manager/resource allocation",
|
|
source: SOURCE_PRESALES_AUX,
|
|
scope: SCOPE_ORG_WIDE,
|
|
}),
|
|
],
|
|
});
|
|
}
|
|
if (name === "opportunities-current-team-close-window") {
|
|
return buildScopeDescriptor({
|
|
scopeMode: SCOPE_MIXED,
|
|
baseScope: SCOPE_ORG_WIDE,
|
|
baseLabel: "Opportunities/workloads",
|
|
baseSource: VIEW_DEFS[definition.view]?.primarySource || null,
|
|
enrichmentScope: SCOPE_MY_TEAM,
|
|
enrichmentLabel: "Resources",
|
|
enrichmentSource: VIEW_DEFS["sa-attach-presales-coverage"]?.primarySource || null,
|
|
fallbackScope: VIEW_DEFS[definition.view]?.fallbackScope || null,
|
|
preserveUnmatchedRows: true,
|
|
components: [
|
|
buildScopeComponent({
|
|
name: "commercialRows",
|
|
label: "Opportunities/workloads",
|
|
source: VIEW_DEFS[definition.view]?.primarySource || null,
|
|
scope: SCOPE_ORG_WIDE,
|
|
}),
|
|
buildScopeComponent({
|
|
name: "resourceCoverage",
|
|
label: "Resources",
|
|
source: SOURCE_PRESALES_AUX,
|
|
scope: SCOPE_MY_TEAM,
|
|
}),
|
|
],
|
|
});
|
|
}
|
|
if (["opportunities-by-resource-close-window", "opportunities-by-manager-close-window"].includes(name)) {
|
|
return buildScopeDescriptor({
|
|
scopeMode: SCOPE_ORG_WIDE,
|
|
baseScope: SCOPE_ORG_WIDE,
|
|
baseLabel: "Opportunities/workloads",
|
|
baseSource: VIEW_DEFS[definition.view]?.primarySource || null,
|
|
enrichmentScope: SCOPE_ORG_WIDE,
|
|
enrichmentLabel: "Manager/resource allocation",
|
|
enrichmentSource: SOURCE_PRESALES_AUX,
|
|
fallbackScope: VIEW_DEFS[definition.view]?.fallbackScope || null,
|
|
preserveUnmatchedRows: true,
|
|
customDisclosureLines: [
|
|
"Opportunities/workloads: org-wide.",
|
|
"Manager/resource allocation: org-wide from Knowledge DV - Presales Involvement Aux - V1.",
|
|
"Rows without a resource match stay in the result with empty resource fields.",
|
|
],
|
|
components: [
|
|
buildScopeComponent({
|
|
name: "commercialRows",
|
|
label: "Opportunities/workloads",
|
|
source: VIEW_DEFS[definition.view]?.primarySource || null,
|
|
scope: SCOPE_ORG_WIDE,
|
|
}),
|
|
buildScopeComponent({
|
|
name: "managerResourceAllocation",
|
|
label: "Manager/resource allocation",
|
|
source: SOURCE_PRESALES_AUX,
|
|
scope: SCOPE_ORG_WIDE,
|
|
}),
|
|
],
|
|
});
|
|
}
|
|
return buildViewScopeDescriptor(definition.view, VIEW_DEFS[definition.view] || {});
|
|
}
|
|
|
|
async function fetchResourceOpportunitySrCoverage({
|
|
resourceEmails,
|
|
opportunityIds,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
limitPerQuery,
|
|
}) {
|
|
const normalizedResources = Array.from(
|
|
new Set(
|
|
(resourceEmails || [])
|
|
.map((email) => String(email || "").trim().toLowerCase())
|
|
.filter(Boolean)
|
|
)
|
|
).sort();
|
|
const matchedOpportunities = new Set((opportunityIds || []).filter(Boolean));
|
|
const srNumbersByResourceOpportunity = new Map();
|
|
const candidateInternalSrDetailsByResource = new Map();
|
|
|
|
if (normalizedResources.length === 0 || matchedOpportunities.size === 0) {
|
|
return {
|
|
srNumbersByResourceOpportunity,
|
|
candidateInternalSrDetailsByResource,
|
|
apiEntries: [],
|
|
rawResults: [],
|
|
};
|
|
}
|
|
|
|
const viewDefinition = VIEW_DEFS["srs-by-resource-email"];
|
|
const querySpecs = normalizedResources.flatMap((resourceEmail) =>
|
|
["team", "lead"].map((matchRole) => ({
|
|
resourceEmail,
|
|
matchRole,
|
|
sql: viewDefinition.buildSql({
|
|
resourceEmailSql: quoteLiteral(resourceEmail),
|
|
matchRole,
|
|
srStatuses: [],
|
|
}),
|
|
}))
|
|
);
|
|
|
|
const queryResults = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
queries: querySpecs.map((query) => ({ sql: query.sql, limit: limitPerQuery })),
|
|
});
|
|
|
|
querySpecs.forEach((query, index) => {
|
|
const records = mapRows(
|
|
SRS_BY_RESOURCE_COLUMNS.map((column) => column.name),
|
|
queryResults[index]?.aResults || []
|
|
);
|
|
for (const record of records) {
|
|
const opportunityId = record?.opportunityId;
|
|
const srNumber = record?.srNumber;
|
|
if (!srNumber || String(srNumber).trim().toLowerCase() === "unspecified") continue;
|
|
if (hasUsableOpportunityId(opportunityId) && matchedOpportunities.has(opportunityId)) {
|
|
const key = buildSrCoverageKey(opportunityId, query.resourceEmail);
|
|
const entry = srNumbersByResourceOpportunity.get(key) || new Set();
|
|
entry.add(srNumber);
|
|
srNumbersByResourceOpportunity.set(key, entry);
|
|
continue;
|
|
}
|
|
if (hasUsableOpportunityId(opportunityId) || String(record?.srSource || "").trim() === "Opportunity") {
|
|
continue;
|
|
}
|
|
const resourceCandidates =
|
|
candidateInternalSrDetailsByResource.get(query.resourceEmail) || new Map();
|
|
if (!resourceCandidates.has(srNumber)) {
|
|
resourceCandidates.set(srNumber, {
|
|
srNumber,
|
|
srStatus: record?.srStatus || null,
|
|
srSource: record?.srSource || null,
|
|
title: record?.title || null,
|
|
serviceDescription: record?.serviceDescription || null,
|
|
customerName: record?.customerName || null,
|
|
queueName: record?.queueName || null,
|
|
internalDeadline: record?.internalDeadline || null,
|
|
totalSrHours: record?.totalSrHours || null,
|
|
lastEffort: record?.lastEffort || null,
|
|
});
|
|
}
|
|
candidateInternalSrDetailsByResource.set(query.resourceEmail, resourceCandidates);
|
|
}
|
|
});
|
|
|
|
return {
|
|
srNumbersByResourceOpportunity,
|
|
candidateInternalSrDetailsByResource,
|
|
apiEntries: querySpecs.map((query, index) => ({
|
|
label: `resourceSrCoverage:${query.resourceEmail}:${query.matchRole}`,
|
|
result: queryResults[index],
|
|
source: "DV - SE Team",
|
|
})),
|
|
rawResults: querySpecs.map((query, index) => ({
|
|
resourceEmail: query.resourceEmail,
|
|
matchRole: query.matchRole,
|
|
result: queryResults[index],
|
|
})),
|
|
};
|
|
}
|
|
|
|
async function executeWorkloadManagerResourceIntent({
|
|
args,
|
|
intent,
|
|
limit,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
}) {
|
|
const dateWindow = resolveDateWindow(args, { from: "today", to: "+7d" });
|
|
const managerFilter = resolveManagerEmailListFilter(args);
|
|
const cluster = resolveCommercialClusterFilter(args);
|
|
const workloadSql = buildManagerResourceWorkloadSql({
|
|
fromDateSql: dateWindow.fromDateSql,
|
|
toDateSql: dateWindow.toDateSql,
|
|
cluster,
|
|
});
|
|
const coverageLimit = resolveCoverageLimit(limit);
|
|
const [workloadResult] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
queries: [{ sql: workloadSql, limit }],
|
|
});
|
|
|
|
const workloadRecords = mapRows(
|
|
WORKLOAD_MANAGER_RESOURCE_PIPELINE_COLUMNS.map((column) => column.name),
|
|
workloadResult?.aResults || []
|
|
);
|
|
const opportunityIds = Array.from(new Set(workloadRecords.map((record) => record.opportunityId).filter(Boolean)));
|
|
|
|
let coverageSql = null;
|
|
let coverageResult = null;
|
|
let coverageRecords = [];
|
|
if (opportunityIds.length > 0) {
|
|
coverageSql = buildManagerResourceCoverageSqlForOpportunityIds(opportunityIds, managerFilter.managerEmails);
|
|
[coverageResult] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
queries: [{ sql: coverageSql, limit: coverageLimit }],
|
|
});
|
|
coverageRecords = mapRows(
|
|
SA_ATTACH_PRESALES_AUX_COLUMNS.map((column) => column.name),
|
|
coverageResult?.aResults || []
|
|
);
|
|
}
|
|
|
|
const coverageSummary = summarizeOrgWideRevenueLineCoverage(workloadRecords, coverageRecords);
|
|
coverageSummary.summary.managersRequested = managerFilter.managerEmails.length;
|
|
|
|
return {
|
|
generatedAt: new Date().toISOString(),
|
|
scope: buildScopeDescriptor({
|
|
scopeMode: SCOPE_ORG_WIDE,
|
|
baseScope: SCOPE_ORG_WIDE,
|
|
baseLabel: "Opportunities/workloads",
|
|
baseSource: SOURCE_OPPORTUNITIES,
|
|
enrichmentScope: SCOPE_ORG_WIDE,
|
|
enrichmentLabel: "Manager/resource allocation",
|
|
enrichmentSource: SOURCE_PRESALES_AUX,
|
|
customDisclosureLines: [
|
|
"Opportunities/workloads: org-wide.",
|
|
"Manager/resource allocation: org-wide from Knowledge DV - Presales Involvement Aux - V1.",
|
|
],
|
|
components: [
|
|
buildScopeComponent({
|
|
name: "commercialRows",
|
|
label: "Opportunities/workloads",
|
|
source: SOURCE_OPPORTUNITIES,
|
|
scope: SCOPE_ORG_WIDE,
|
|
}),
|
|
buildScopeComponent({
|
|
name: "managerResourceAllocation",
|
|
label: "Manager/resource allocation",
|
|
source: SOURCE_PRESALES_AUX,
|
|
scope: SCOPE_ORG_WIDE,
|
|
}),
|
|
],
|
|
}),
|
|
apiMetrics: buildApiMetrics([
|
|
{
|
|
label: "workloadRevenueLines",
|
|
result: workloadResult,
|
|
source: SOURCE_OPPORTUNITIES,
|
|
},
|
|
...(coverageResult
|
|
? [
|
|
{
|
|
label: "managerResourceCoverage",
|
|
result: coverageResult,
|
|
source: SOURCE_PRESALES_AUX,
|
|
},
|
|
]
|
|
: []),
|
|
]),
|
|
request: {
|
|
mode: "intent",
|
|
intent,
|
|
view: "sa-attach-pipeline-details",
|
|
secondaryView: "sa-attach-presales-coverage",
|
|
endpoint: new URL(SQL_ENDPOINT, baseUrl).toString(),
|
|
statePath,
|
|
metaPath,
|
|
scopeMode: SCOPE_ORG_WIDE,
|
|
matchStrategy:
|
|
"Filter org-wide workload revenue lines by Revenue Line Close Date, then keep only opportunities that have Presales Involvement Aux rows with SE Team Level 4 in the requested manager list and SE Team Level 5 populated as the allocated resource.",
|
|
managerEmails: managerFilter.managerEmails,
|
|
cluster,
|
|
datePreset: dateWindow.preset,
|
|
fromDate: dateWindow.fromDate,
|
|
fromDateInput: dateWindow.fromDateInput,
|
|
toDate: dateWindow.toDate,
|
|
toDateInput: dateWindow.toDateInput,
|
|
...(dateWindow.fiscalQuarter
|
|
? {
|
|
fiscalYear: dateWindow.fiscalYear,
|
|
fiscalQuarter: dateWindow.fiscalQuarter,
|
|
}
|
|
: {}),
|
|
...(dateWindow.week
|
|
? {
|
|
week: dateWindow.week,
|
|
weekLabel: dateWindow.weekLabel,
|
|
weekYear: dateWindow.weekYear,
|
|
}
|
|
: {}),
|
|
limit,
|
|
coverageLimit,
|
|
useCache,
|
|
dataSource: "Knowledge DV - Opportunities - V2",
|
|
coverageSource: "Knowledge DV - Presales Involvement Aux - V1",
|
|
sql: {
|
|
workloadRevenueLines: workloadSql,
|
|
managerResourceCoverage: coverageSql,
|
|
},
|
|
},
|
|
columns: WORKLOAD_MANAGER_RESOURCE_CLOSE_WINDOW_COLUMNS,
|
|
records: coverageSummary.records,
|
|
summary: coverageSummary.summary,
|
|
warnings: [
|
|
...buildWarnings({
|
|
requestedLimit: limit,
|
|
recordsCount: workloadResult?.aResults?.length || 0,
|
|
customSql: false,
|
|
}),
|
|
...(coverageResult
|
|
? buildWarnings({
|
|
requestedLimit: coverageLimit,
|
|
recordsCount: coverageResult?.aResults?.length || 0,
|
|
customSql: false,
|
|
})
|
|
: []),
|
|
"Workload rows stay org-wide and do not fall back to DV - SE Team.",
|
|
"Manager/resource linkage is taken only from Knowledge DV - Presales Involvement Aux - V1 using SE Team Level 4 as manager and SE Team Level 5 as resource.",
|
|
],
|
|
raw: {
|
|
workloadRevenueLines: workloadResult,
|
|
managerResourceCoverage: coverageResult,
|
|
},
|
|
};
|
|
}
|
|
|
|
async function executeWorkloadResourceIntent({
|
|
args,
|
|
intent,
|
|
limit,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
}) {
|
|
const effectiveArgs = applyCloseWindowIntentDefaults(args);
|
|
const dateWindow = resolveDateWindow(effectiveArgs, { from: "today", to: "+7d" });
|
|
const resourceFilter = resolveResourceFilter(effectiveArgs);
|
|
const cluster = resolveCommercialClusterFilter(effectiveArgs);
|
|
const forecastTypes = splitCsv(effectiveArgs["forecast-type"], []);
|
|
const workloadSql = buildWorkloadCloseWindowSql({
|
|
fromDateSql: dateWindow.fromDateSql,
|
|
toDateSql: dateWindow.toDateSql,
|
|
cluster,
|
|
forecastTypes,
|
|
});
|
|
const coverageLimit = resolveCoverageLimit(limit);
|
|
const [workloadResult] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
queries: [{ sql: workloadSql, limit }],
|
|
});
|
|
|
|
const workloadRecords = mapRows(
|
|
WORKLOAD_MANAGER_RESOURCE_PIPELINE_COLUMNS.map((column) => column.name),
|
|
workloadResult?.aResults || []
|
|
);
|
|
const opportunityIds = Array.from(new Set(workloadRecords.map((record) => record.opportunityId).filter(Boolean)));
|
|
let coverageSql = null;
|
|
let coverageResult = null;
|
|
let coverageRecords = [];
|
|
if (opportunityIds.length > 0) {
|
|
coverageSql = buildExplicitCoverageSqlForOpportunityIds(opportunityIds, resourceFilter.resourceEmailSql);
|
|
[coverageResult] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
queries: [{ sql: coverageSql, limit: coverageLimit }],
|
|
});
|
|
coverageRecords = mapRows(
|
|
SA_ATTACH_PRESALES_AUX_COLUMNS.map((column) => column.name),
|
|
coverageResult?.aResults || []
|
|
);
|
|
}
|
|
|
|
const coverageSummary = summarizeOrgWideRevenueLineCoverage(workloadRecords, coverageRecords);
|
|
coverageSummary.summary.resourcesRequested = 1;
|
|
|
|
return {
|
|
generatedAt: new Date().toISOString(),
|
|
scope: buildScopeDescriptor({
|
|
scopeMode: SCOPE_ORG_WIDE,
|
|
baseScope: SCOPE_ORG_WIDE,
|
|
baseLabel: "Opportunities/workloads",
|
|
baseSource: SOURCE_OPPORTUNITIES,
|
|
enrichmentScope: SCOPE_ORG_WIDE,
|
|
enrichmentLabel: "Manager/resource allocation",
|
|
enrichmentSource: SOURCE_PRESALES_AUX,
|
|
customDisclosureLines: [
|
|
"Opportunities/workloads: org-wide.",
|
|
"Manager/resource allocation: org-wide from Knowledge DV - Presales Involvement Aux - V1.",
|
|
],
|
|
components: [
|
|
buildScopeComponent({
|
|
name: "commercialRows",
|
|
label: "Opportunities/workloads",
|
|
source: SOURCE_OPPORTUNITIES,
|
|
scope: SCOPE_ORG_WIDE,
|
|
}),
|
|
buildScopeComponent({
|
|
name: "managerResourceAllocation",
|
|
label: "Manager/resource allocation",
|
|
source: SOURCE_PRESALES_AUX,
|
|
scope: SCOPE_ORG_WIDE,
|
|
}),
|
|
],
|
|
}),
|
|
apiMetrics: buildApiMetrics([
|
|
{ label: "workloadRevenueLines", result: workloadResult, source: SOURCE_OPPORTUNITIES },
|
|
...(coverageResult ? [{ label: "resourceCoverage", result: coverageResult, source: SOURCE_PRESALES_AUX }] : []),
|
|
]),
|
|
request: {
|
|
mode: "intent",
|
|
intent,
|
|
view: "sa-attach-pipeline-details",
|
|
secondaryView: "sa-attach-presales-coverage",
|
|
endpoint: new URL(SQL_ENDPOINT, baseUrl).toString(),
|
|
statePath,
|
|
metaPath,
|
|
scopeMode: SCOPE_ORG_WIDE,
|
|
matchStrategy:
|
|
"Filter org-wide workload revenue lines by Revenue Line Close Date, then keep only opportunities where the requested resource appears in Presales Involvement Aux coverage.",
|
|
resourceInput: resourceFilter.resourceInput,
|
|
resourceEmail: resourceFilter.resourceEmail,
|
|
cluster,
|
|
forecastTypes,
|
|
datePreset: dateWindow.preset,
|
|
fromDate: dateWindow.fromDate,
|
|
fromDateInput: dateWindow.fromDateInput,
|
|
toDate: dateWindow.toDate,
|
|
toDateInput: dateWindow.toDateInput,
|
|
...(dateWindow.fiscalQuarter
|
|
? {
|
|
fiscalYear: dateWindow.fiscalYear,
|
|
fiscalQuarter: dateWindow.fiscalQuarter,
|
|
}
|
|
: {}),
|
|
...(dateWindow.week
|
|
? {
|
|
week: dateWindow.week,
|
|
weekLabel: dateWindow.weekLabel,
|
|
weekYear: dateWindow.weekYear,
|
|
}
|
|
: {}),
|
|
limit,
|
|
coverageLimit,
|
|
useCache,
|
|
dataSource: SOURCE_OPPORTUNITIES,
|
|
coverageSource: SOURCE_PRESALES_AUX,
|
|
sql: {
|
|
workloadRevenueLines: workloadSql,
|
|
resourceCoverage: coverageSql,
|
|
},
|
|
},
|
|
columns: WORKLOAD_MANAGER_RESOURCE_CLOSE_WINDOW_COLUMNS,
|
|
records: coverageSummary.records,
|
|
summary: coverageSummary.summary,
|
|
warnings: [
|
|
...buildWarnings({
|
|
requestedLimit: limit,
|
|
recordsCount: workloadResult?.aResults?.length || 0,
|
|
customSql: false,
|
|
}),
|
|
...(coverageResult
|
|
? buildWarnings({
|
|
requestedLimit: coverageLimit,
|
|
recordsCount: coverageResult?.aResults?.length || 0,
|
|
customSql: false,
|
|
})
|
|
: []),
|
|
"Workload rows stay org-wide and do not fall back to DV - SE Team.",
|
|
"Resource linkage is taken only from Knowledge DV - Presales Involvement Aux - V1.",
|
|
],
|
|
raw: {
|
|
workloadRevenueLines: workloadResult,
|
|
resourceCoverage: coverageResult,
|
|
},
|
|
};
|
|
}
|
|
|
|
async function executeOpportunityCoverageIntent({
|
|
args,
|
|
intent,
|
|
resolvedView,
|
|
limit,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
coverageSqlFactory,
|
|
matchStrategy,
|
|
requestScope = {},
|
|
coverageRequestKey = "coverage",
|
|
coverageScope = SCOPE_MY_TEAM,
|
|
extraWarnings = [],
|
|
allowCommercialFallback = false,
|
|
}) {
|
|
const definition = VIEW_DEFS["opportunities-close-window"];
|
|
const dateWindow = resolveDateWindow(args, { from: "today", to: "+7d" });
|
|
const statuses = splitCsv(args["opty-status"], definition.defaultStatuses);
|
|
const explicitCluster = resolveOptionalClusterFilter(args);
|
|
const primarySql = definition.buildSql({
|
|
statuses,
|
|
...dateWindow,
|
|
cluster: explicitCluster,
|
|
});
|
|
const fallbackSql = allowCommercialFallback && definition.buildFallbackSql
|
|
? definition.buildFallbackSql({
|
|
statuses,
|
|
...dateWindow,
|
|
})
|
|
: null;
|
|
const coverageLimit = resolveCoverageLimit(limit);
|
|
const [windowExecution, sessionContextResolution] = await Promise.all([
|
|
executeQueryWithFallback({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
primarySql,
|
|
fallbackSql,
|
|
limit,
|
|
primarySource: definition.primarySource,
|
|
fallbackSource: definition.fallbackSource,
|
|
}),
|
|
resolveSessionContext({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
autoAuthRefresh: true,
|
|
useCache,
|
|
includeApiMetrics: true,
|
|
}),
|
|
]);
|
|
const opportunityRecords = mapRows(definition.fields, windowExecution.result?.aResults || []);
|
|
const opportunityIds = Array.from(new Set(opportunityRecords.map((record) => record.opportunityId).filter(Boolean)));
|
|
|
|
let coverageSql = null;
|
|
let coverageResult = null;
|
|
let coverageRecords = [];
|
|
if (opportunityIds.length > 0) {
|
|
coverageSql = coverageSqlFactory(opportunityIds);
|
|
[coverageResult] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
queries: [{ sql: coverageSql, limit: coverageLimit }],
|
|
});
|
|
coverageRecords = mapRows(SA_ATTACH_PRESALES_AUX_COLUMNS.map((column) => column.name), coverageResult?.aResults || []);
|
|
}
|
|
|
|
const sessionContext = sessionContextResolution.sessionContext || {
|
|
securityEntryPoint: null,
|
|
};
|
|
const coverageSummary = summarizeCurrentTeamOpportunityCoverage(opportunityRecords, coverageRecords);
|
|
const baseScope = inferResolvedScopeMode(resolvedView, definition, {
|
|
source: windowExecution.source,
|
|
fallbackUsed: windowExecution.fallbackUsed,
|
|
});
|
|
const scope = buildScopeDescriptor({
|
|
scopeMode:
|
|
baseScope === SCOPE_ORG_WIDE && coverageScope !== baseScope
|
|
? SCOPE_MIXED
|
|
: baseScope,
|
|
baseScope,
|
|
baseLabel: "Opportunities/workloads",
|
|
baseSource: windowExecution.source || definition.primarySource || null,
|
|
enrichmentScope: coverageScope,
|
|
enrichmentLabel: coverageScope === SCOPE_ORG_WIDE ? "Manager/resource allocation" : "Resources",
|
|
enrichmentSource: VIEW_DEFS["sa-attach-presales-coverage"].primarySource || null,
|
|
fallbackUsed: windowExecution.fallbackUsed,
|
|
fallbackSource: definition.fallbackSource || null,
|
|
fallbackScope: definition.fallbackScope || null,
|
|
preserveUnmatchedRows: baseScope === SCOPE_ORG_WIDE,
|
|
customDisclosureLines:
|
|
baseScope === SCOPE_ORG_WIDE && coverageScope === SCOPE_ORG_WIDE
|
|
? [
|
|
"Opportunities/workloads: org-wide.",
|
|
"Manager/resource allocation: org-wide from Knowledge DV - Presales Involvement Aux - V1.",
|
|
"Rows without a resource match stay in the result with empty resource fields.",
|
|
]
|
|
: null,
|
|
components: [
|
|
buildScopeComponent({
|
|
name: "commercialRows",
|
|
label: "Opportunities/workloads",
|
|
source: windowExecution.source || definition.primarySource || null,
|
|
scope: baseScope,
|
|
}),
|
|
buildScopeComponent({
|
|
name: coverageScope === SCOPE_ORG_WIDE ? "managerResourceAllocation" : "resourceCoverage",
|
|
label: coverageScope === SCOPE_ORG_WIDE ? "Manager/resource allocation" : "Resources",
|
|
source: VIEW_DEFS["sa-attach-presales-coverage"].primarySource || null,
|
|
scope: coverageScope,
|
|
}),
|
|
],
|
|
});
|
|
const apiMetrics = buildApiMetrics([
|
|
{
|
|
label: "opportunityWindow",
|
|
result: windowExecution.result,
|
|
source: windowExecution.source || definition.primarySource || null,
|
|
},
|
|
{
|
|
label: "sessionContext",
|
|
result: sessionContextResolution.source === "session-meta-cache" ? null : { aResults: [[sessionContext.securityEntryPoint]] },
|
|
source: "DV - SE Team",
|
|
rowsReturned: sessionContextResolution.source === "session-meta-cache" ? 0 : 1,
|
|
countedInTotal: sessionContextResolution.source !== "session-meta-cache",
|
|
},
|
|
...(coverageResult
|
|
? [
|
|
{
|
|
label: coverageRequestKey,
|
|
result: coverageResult,
|
|
source: VIEW_DEFS["sa-attach-presales-coverage"].primarySource || null,
|
|
},
|
|
]
|
|
: []),
|
|
]);
|
|
|
|
return {
|
|
generatedAt: new Date().toISOString(),
|
|
scope,
|
|
apiMetrics,
|
|
request: {
|
|
mode: "intent",
|
|
intent,
|
|
view: resolvedView,
|
|
secondaryView: "sa-attach-presales-coverage",
|
|
endpoint: new URL(SQL_ENDPOINT, baseUrl).toString(),
|
|
statePath,
|
|
metaPath,
|
|
securityEntryPoint: sessionContext.securityEntryPoint,
|
|
matchStrategy,
|
|
...(explicitCluster ? { cluster: explicitCluster } : {}),
|
|
datePreset: dateWindow.preset,
|
|
fromDate: dateWindow.fromDate,
|
|
fromDateInput: dateWindow.fromDateInput,
|
|
toDate: dateWindow.toDate,
|
|
toDateInput: dateWindow.toDateInput,
|
|
...(dateWindow.fiscalQuarter
|
|
? {
|
|
fiscalYear: dateWindow.fiscalYear,
|
|
fiscalQuarter: dateWindow.fiscalQuarter,
|
|
}
|
|
: {}),
|
|
...(dateWindow.week
|
|
? {
|
|
week: dateWindow.week,
|
|
weekLabel: dateWindow.weekLabel,
|
|
weekYear: dateWindow.weekYear,
|
|
}
|
|
: {}),
|
|
statuses,
|
|
limit,
|
|
coverageLimit,
|
|
scopeMode: scope.scopeMode,
|
|
coverageScope,
|
|
dataSource: windowExecution.source,
|
|
fallbackUsed: windowExecution.fallbackUsed,
|
|
useCache,
|
|
...requestScope,
|
|
sql: {
|
|
opportunityWindow: windowExecution.sql,
|
|
...(windowExecution.primaryError ? { opportunityWindowPrimaryError: windowExecution.primaryError } : {}),
|
|
sessionContext:
|
|
sessionContextResolution.source === "session-meta-cache"
|
|
? "session-meta-cache"
|
|
: VIEW_DEFS["data-quality-session-context"].buildSql(),
|
|
[coverageRequestKey]: coverageSql,
|
|
},
|
|
},
|
|
columns: CURRENT_TEAM_CLOSE_WINDOW_COLUMNS,
|
|
records: coverageSummary.records,
|
|
summary: coverageSummary.summary,
|
|
warnings: [
|
|
...buildWarnings({
|
|
requestedLimit: limit,
|
|
recordsCount: windowExecution.result?.aResults?.length || 0,
|
|
customSql: false,
|
|
}),
|
|
...windowExecution.warnings,
|
|
...(coverageResult
|
|
? buildWarnings({
|
|
requestedLimit: coverageLimit,
|
|
recordsCount: coverageResult?.aResults?.length || 0,
|
|
customSql: false,
|
|
})
|
|
: []),
|
|
...extraWarnings,
|
|
],
|
|
raw: {
|
|
opportunityWindow: windowExecution.result,
|
|
sessionContext,
|
|
[coverageRequestKey]: coverageResult,
|
|
},
|
|
};
|
|
}
|
|
|
|
async function resolveFiscalYear({
|
|
args,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath = null,
|
|
metaPath = null,
|
|
autoAuthRefresh = true,
|
|
includeApiMetrics = false,
|
|
}) {
|
|
if (args["fiscal-year"]) {
|
|
return includeApiMetrics
|
|
? { fiscalYear: args["fiscal-year"], apiMetrics: buildApiMetrics() }
|
|
: args["fiscal-year"];
|
|
}
|
|
if (hasFreshFiscalYearCache(meta)) {
|
|
return includeApiMetrics
|
|
? { fiscalYear: meta.currentFiscalYear, apiMetrics: buildApiMetrics() }
|
|
: meta.currentFiscalYear;
|
|
}
|
|
const [result] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
autoAuthRefresh,
|
|
queries: [{ sql: VIEW_DEFS["current-fiscal-year"].buildSql(), limit: 1 }],
|
|
});
|
|
const value = result?.aResults?.[0]?.[0];
|
|
if (!value) {
|
|
throw new Error("Could not resolve the current fiscal year from Oracle DV.");
|
|
}
|
|
return includeApiMetrics
|
|
? {
|
|
fiscalYear: value,
|
|
apiMetrics: buildApiMetrics([
|
|
{
|
|
label: "currentFiscalYear",
|
|
result,
|
|
source: "DV - SE Team",
|
|
},
|
|
]),
|
|
}
|
|
: value;
|
|
}
|
|
|
|
async function persistResolvedFiscalYear(metaPath, meta, fiscalYear) {
|
|
if (!fiscalYear || meta.currentFiscalYear === fiscalYear) {
|
|
return meta;
|
|
}
|
|
const nextMeta = {
|
|
...meta,
|
|
currentFiscalYear: fiscalYear,
|
|
currentFiscalYearCapturedAt: new Date().toISOString(),
|
|
};
|
|
await ensureParentDir(metaPath);
|
|
await fs.writeFile(metaPath, `${JSON.stringify(nextMeta, null, 2)}\n`, "utf8");
|
|
return nextMeta;
|
|
}
|
|
|
|
async function persistResolvedSessionContext(metaPath, meta, sessionContext) {
|
|
if (!sessionContext || typeof sessionContext !== "object") {
|
|
return meta;
|
|
}
|
|
const nextMeta = {
|
|
...meta,
|
|
currentSessionContext: sessionContext,
|
|
currentSessionContextCapturedAt: new Date().toISOString(),
|
|
};
|
|
await ensureParentDir(metaPath);
|
|
await fs.writeFile(metaPath, `${JSON.stringify(nextMeta, null, 2)}\n`, "utf8");
|
|
return nextMeta;
|
|
}
|
|
|
|
async function resolveSessionContext({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
autoAuthRefresh = true,
|
|
useCache = true,
|
|
includeApiMetrics = false,
|
|
}) {
|
|
if (useCache && hasFreshSessionContextCache(meta)) {
|
|
return includeApiMetrics
|
|
? { sessionContext: meta.currentSessionContext, apiMetrics: buildApiMetrics(), source: "session-meta-cache" }
|
|
: meta.currentSessionContext;
|
|
}
|
|
const [result] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
autoAuthRefresh,
|
|
useCache,
|
|
queries: [{ sql: VIEW_DEFS["data-quality-session-context"].buildSql(), limit: 1 }],
|
|
});
|
|
const sessionContext = mapRows(VIEW_DEFS["data-quality-session-context"].fields, result?.aResults || [])[0] || {
|
|
securityEntryPoint: null,
|
|
};
|
|
if (useCache) {
|
|
await persistResolvedSessionContext(metaPath, meta, sessionContext);
|
|
}
|
|
return includeApiMetrics
|
|
? {
|
|
sessionContext,
|
|
apiMetrics: buildApiMetrics([
|
|
{
|
|
label: "sessionContext",
|
|
result,
|
|
source: "DV - SE Team",
|
|
},
|
|
]),
|
|
source: "live-query",
|
|
}
|
|
: sessionContext;
|
|
}
|
|
|
|
function normalizeMeta(metaPath, meta) {
|
|
for (const key of ["xCsrfToken", "xBitechClientbin"]) {
|
|
if (!meta?.[key]) {
|
|
throw new Error(`Session metadata at ${metaPath} is missing ${key}. Refresh auth state first.`);
|
|
}
|
|
}
|
|
return meta;
|
|
}
|
|
|
|
async function main() {
|
|
const args = parseArgs(process.argv.slice(2));
|
|
if (args.help === "true") {
|
|
console.log(usage());
|
|
return;
|
|
}
|
|
|
|
if (args.catalog === "true") {
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
...summarizeCatalog(),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["list-views"] === "true") {
|
|
const payload = {
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
views: viewNames().map((name) => summarizeView(name, VIEW_DEFS[name])),
|
|
};
|
|
console.log(JSON.stringify(payload, null, 2));
|
|
return;
|
|
}
|
|
|
|
if (args["list-intents"] === "true") {
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
intents: intentNames().map((name) => summarizeIntent(name, INTENT_CATALOG[name])),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["list-parameters"] === "true") {
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
parameters: summarizeParameters(),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["describe-parameter"]) {
|
|
const parameter = findParameter(args["describe-parameter"]);
|
|
if (!parameter) {
|
|
throw new Error(
|
|
`Unknown parameter "${args["describe-parameter"]}". Use --list-parameters to inspect the local catalog.`
|
|
);
|
|
}
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
parameter: summarizeParameter(parameter),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["list-scripts"] === "true") {
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
scripts: summarizeScripts(),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["describe-script"]) {
|
|
const script = findScript(args["describe-script"]);
|
|
if (!script) {
|
|
throw new Error(
|
|
`Unknown script "${args["describe-script"]}". Use --list-scripts to inspect the local catalog.`
|
|
);
|
|
}
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
script: summarizeScript(script),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["list-entities"] === "true") {
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
entities: summarizeEntities(),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["list-fields"] === "true") {
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
fields: listFields({
|
|
entity: args.entity || null,
|
|
role: args.role || null,
|
|
search: args.search || null,
|
|
}),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["describe-field"]) {
|
|
const field = findField(args["describe-field"]);
|
|
if (!field) {
|
|
throw new Error(
|
|
`Unknown field "${args["describe-field"]}". Use --list-fields to inspect the local catalog.`
|
|
);
|
|
}
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
field: summarizeField(field),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["describe-intent"]) {
|
|
const definition = INTENT_CATALOG[args["describe-intent"]];
|
|
if (!definition) {
|
|
throw new Error(
|
|
`Unknown intent "${args["describe-intent"]}". Supported intents: ${intentNames().join(", ")}`
|
|
);
|
|
}
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
intent: summarizeIntent(args["describe-intent"], definition),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
if (args["describe-view"]) {
|
|
const definition = VIEW_DEFS[args["describe-view"]];
|
|
if (!definition) {
|
|
const suggestion = suggestViewName(args["describe-view"]);
|
|
throw new Error(
|
|
suggestion
|
|
? `Unknown view "${args["describe-view"]}". Did you mean "${suggestion}"?`
|
|
: `Unknown view "${args["describe-view"]}". Supported views: ${viewNames().join(", ")}`
|
|
);
|
|
}
|
|
console.log(
|
|
JSON.stringify(
|
|
{
|
|
generatedAt: new Date().toISOString(),
|
|
apiMetrics: buildApiMetrics(),
|
|
view: summarizeView(args["describe-view"], definition),
|
|
},
|
|
null,
|
|
2
|
|
)
|
|
);
|
|
return;
|
|
}
|
|
|
|
const view = args.view || null;
|
|
const customSql = args.sql || null;
|
|
const intent = args.intent || null;
|
|
const queryMode = args.query === "true" || Boolean(args.select || args.measure);
|
|
const renderSqlOnly = args["render-sql"] === "true";
|
|
const checkAuthOnly = args["check-auth"] === "true";
|
|
const limit = parseLimit(args.limit);
|
|
const useCache = args["no-cache"] !== "true";
|
|
const runtimeHome = resolveRuntimeHome(args["runtime-home"]);
|
|
const requestedScope = normalizeScopeInput(args.scope || SCOPE_AUTO);
|
|
|
|
if (renderSqlOnly && !customSql && !queryMode) {
|
|
throw new Error("Pass --sql or --query together with --render-sql.");
|
|
}
|
|
if (queryMode && customSql) {
|
|
throw new Error("Use either --query or --sql, not both.");
|
|
}
|
|
if (queryMode && intent) {
|
|
throw new Error("Use either --query or --intent, not both.");
|
|
}
|
|
if (queryMode && view) {
|
|
throw new Error("Use either --query or --view, not both.");
|
|
}
|
|
if (intent && customSql) {
|
|
throw new Error("Use either --intent or --sql, not both.");
|
|
}
|
|
if (intent && view) {
|
|
throw new Error("Use either --intent or --view, not both.");
|
|
}
|
|
if (!customSql && !view && !intent && !queryMode && !checkAuthOnly) {
|
|
throw new Error("Pass --query, --intent <name>, --view <name>, or --sql <logical sql>.");
|
|
}
|
|
if (customSql && view) {
|
|
throw new Error("Use either --view or --sql, not both.");
|
|
}
|
|
if (queryMode && !args.select && !args.measure) {
|
|
throw new Error("Pass --select and/or --measure when using --query.");
|
|
}
|
|
|
|
if (queryMode) {
|
|
const declarative = buildQueryModeSql(args);
|
|
if (renderSqlOnly) {
|
|
const payload = {
|
|
generatedAt: new Date().toISOString(),
|
|
mode: "render-sql",
|
|
requestMode: "query",
|
|
query: declarative.querySpec,
|
|
scope: buildScopeDescriptor({
|
|
scopeMode: declarative.querySpec.effectiveScope,
|
|
baseScope: declarative.querySpec.effectiveScope,
|
|
baseLabel: "Records",
|
|
baseSource: declarative.querySpec.source,
|
|
requestedScope: declarative.querySpec.requestedScope,
|
|
components: [
|
|
buildScopeComponent({
|
|
name: "records",
|
|
source: declarative.querySpec.source,
|
|
scope: declarative.querySpec.effectiveScope,
|
|
}),
|
|
],
|
|
}),
|
|
expandedSql: declarative.sql,
|
|
columns: declarative.columns,
|
|
templateTokens: declarative.templateTokens,
|
|
};
|
|
const json = await emitJson(args, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
}
|
|
|
|
if (customSql) {
|
|
const rendered = expandSqlTemplate(customSql);
|
|
if (renderSqlOnly) {
|
|
const inferredColumns = inferCustomSqlColumns(customSql, rendered.sql);
|
|
const payload = {
|
|
generatedAt: new Date().toISOString(),
|
|
mode: "render-sql",
|
|
inputSql: customSql,
|
|
expandedSql: rendered.sql,
|
|
templateTokens: rendered.templateTokens,
|
|
inferredColumns,
|
|
};
|
|
const json = await emitJson(args, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
}
|
|
|
|
await ensureRuntimeTree(runtimeHome);
|
|
const statePath = resolveStateReadPath({ explicitPath: args["state-path"], runtimeHome });
|
|
const metaPath = resolveMetaReadPath({ explicitPath: args["meta-path"], runtimeHome });
|
|
const baseUrl = args["base-url"] || process.env.ORACLE_SI_BASE_URL || DEFAULT_BASE_URL;
|
|
const { state, meta } = await loadSessionArtifacts({
|
|
statePath,
|
|
metaPath,
|
|
targetUrl: DEFAULT_WORKBOOK_URL,
|
|
allowInteractiveRefresh: !checkAuthOnly,
|
|
});
|
|
|
|
if (args["check-auth"] === "true") {
|
|
const startedAt = Date.now();
|
|
try {
|
|
const fiscalYearResolution = await resolveFiscalYear({
|
|
args,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
autoAuthRefresh: false,
|
|
includeApiMetrics: true,
|
|
});
|
|
const fiscalYear = fiscalYearResolution.fiscalYear;
|
|
const fiscalYearSource = args["fiscal-year"]
|
|
? "explicit"
|
|
: hasFreshFiscalYearCache(meta)
|
|
? "session-meta-cache"
|
|
: "live-query";
|
|
if (fiscalYearSource === "live-query") {
|
|
await persistResolvedFiscalYear(metaPath, meta, fiscalYear);
|
|
}
|
|
const payload = {
|
|
generatedAt: new Date().toISOString(),
|
|
ok: true,
|
|
endpoint: new URL(SQL_ENDPOINT, baseUrl).toString(),
|
|
statePath,
|
|
metaPath,
|
|
fiscalYear,
|
|
fiscalYearSource,
|
|
elapsedMs: Date.now() - startedAt,
|
|
apiMetrics: fiscalYearResolution.apiMetrics,
|
|
};
|
|
const json = await emitJson(args, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
} catch (error) {
|
|
const payload = {
|
|
generatedAt: new Date().toISOString(),
|
|
ok: false,
|
|
endpoint: new URL(SQL_ENDPOINT, baseUrl).toString(),
|
|
statePath,
|
|
metaPath,
|
|
elapsedMs: Date.now() - startedAt,
|
|
error: error.message,
|
|
apiMetrics: buildApiMetrics(),
|
|
};
|
|
const json = await emitJson(args, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
}
|
|
|
|
if (queryMode) {
|
|
const declarative = buildQueryModeSql(args);
|
|
const [result] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
autoAuthRefresh: true,
|
|
useCache,
|
|
queries: [{ sql: declarative.sql, limit }],
|
|
});
|
|
const rawRows = result?.aResults || [];
|
|
const payload = {
|
|
generatedAt: new Date().toISOString(),
|
|
scope: buildScopeDescriptor({
|
|
scopeMode: declarative.querySpec.effectiveScope,
|
|
baseScope: declarative.querySpec.effectiveScope,
|
|
baseLabel: "Records",
|
|
baseSource: declarative.querySpec.source,
|
|
requestedScope: declarative.querySpec.requestedScope,
|
|
components: [
|
|
buildScopeComponent({
|
|
name: "records",
|
|
source: declarative.querySpec.source,
|
|
scope: declarative.querySpec.effectiveScope,
|
|
}),
|
|
],
|
|
}),
|
|
apiMetrics: buildApiMetrics([
|
|
{
|
|
label: "query",
|
|
result,
|
|
},
|
|
]),
|
|
request: {
|
|
mode: "query",
|
|
endpoint: new URL(SQL_ENDPOINT, baseUrl).toString(),
|
|
statePath,
|
|
metaPath,
|
|
limit,
|
|
useCache,
|
|
requestedScope: declarative.querySpec.requestedScope,
|
|
scopeMode: declarative.querySpec.effectiveScope,
|
|
dataSource: declarative.querySpec.source,
|
|
query: declarative.querySpec,
|
|
sql: declarative.sql,
|
|
templateTokens: declarative.templateTokens,
|
|
},
|
|
columns: declarative.columns,
|
|
records: mapRows(declarative.columns, rawRows),
|
|
warnings: buildWarnings({
|
|
requestedLimit: limit,
|
|
recordsCount: rawRows.length,
|
|
customSql: false,
|
|
}),
|
|
raw: result,
|
|
};
|
|
const json = await emitJson(args, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
|
|
if (customSql) {
|
|
const rendered = expandSqlTemplate(customSql);
|
|
const inferredColumns = inferCustomSqlColumns(customSql, rendered.sql);
|
|
const [result] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
autoAuthRefresh: true,
|
|
useCache,
|
|
queries: [{ sql: rendered.sql, limit }],
|
|
});
|
|
const rawRows = result?.aResults || [];
|
|
const columnNamesResolved =
|
|
inferredColumns.length > 0 &&
|
|
(result?.aColumnTypes?.length || rawRows[0]?.length || 0) === inferredColumns.length;
|
|
const payload = {
|
|
generatedAt: new Date().toISOString(),
|
|
scope: buildScopeDescriptor({
|
|
scopeMode: "unknown",
|
|
baseScope: "unknown",
|
|
baseLabel: "Records",
|
|
}),
|
|
apiMetrics: buildApiMetrics([
|
|
{
|
|
label: "customSql",
|
|
result,
|
|
},
|
|
]),
|
|
request: {
|
|
mode: "custom-sql",
|
|
endpoint: new URL(SQL_ENDPOINT, baseUrl).toString(),
|
|
statePath,
|
|
metaPath,
|
|
limit,
|
|
useCache,
|
|
scopeMode: "unknown",
|
|
inputSql: customSql,
|
|
sql: rendered.sql,
|
|
templateTokens: rendered.templateTokens,
|
|
inferredColumns,
|
|
},
|
|
columns: columnNamesResolved ? inferredColumns : [],
|
|
records: columnNamesResolved ? mapRows(inferredColumns, rawRows) : rawRows,
|
|
warnings: buildWarnings({
|
|
requestedLimit: limit,
|
|
recordsCount: rawRows.length,
|
|
customSql: true,
|
|
customSqlColumnsResolved: columnNamesResolved,
|
|
}),
|
|
raw: result,
|
|
};
|
|
const json = await emitJson(args, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
|
|
const resolvedView = intent ? INTENT_CATALOG[intent]?.view : view;
|
|
if (intent && !resolvedView) {
|
|
throw new Error(`Unknown intent "${intent}". Supported intents: ${intentNames().join(", ")}`);
|
|
}
|
|
let effectiveArgs = args;
|
|
|
|
if (intent === "workloads-by-owner-current") {
|
|
resolveOwnerEmailFilter(args, "owner-email", "Pass --owner-email when using workloads-by-owner-current.");
|
|
effectiveArgs = applyCurrentWorkloadIntentDefaults(args);
|
|
}
|
|
|
|
if (intent === "workloads-by-owner-manager-current") {
|
|
resolveOwnerEmailFilter(
|
|
args,
|
|
"owner-manager-email",
|
|
"Pass --owner-manager-email when using workloads-by-owner-manager-current."
|
|
);
|
|
effectiveArgs = applyCurrentWorkloadIntentDefaults(args);
|
|
}
|
|
|
|
if (intent === "opportunities-by-owner-close-window") {
|
|
const ownerFilter = resolveOwnerEmailFilter(
|
|
args,
|
|
"owner-email",
|
|
"Pass --owner-email when using opportunities-by-owner-close-window."
|
|
);
|
|
effectiveArgs = { ...effectiveArgs, ownerEmail: ownerFilter.ownerEmail };
|
|
}
|
|
|
|
if (intent === "opportunities-by-owner-manager-close-window") {
|
|
const ownerFilter = resolveOwnerEmailFilter(
|
|
args,
|
|
"owner-manager-email",
|
|
"Pass --owner-manager-email when using opportunities-by-owner-manager-close-window."
|
|
);
|
|
effectiveArgs = { ...effectiveArgs, ownerManagerEmail: ownerFilter.ownerEmail };
|
|
}
|
|
|
|
if (intent === "workloads-by-owner-close-window") {
|
|
resolveOwnerEmailFilter(args, "owner-email", "Pass --owner-email when using workloads-by-owner-close-window.");
|
|
effectiveArgs = applyCloseWindowIntentDefaults(args);
|
|
}
|
|
|
|
if (intent === "workloads-by-owner-manager-close-window") {
|
|
resolveOwnerEmailFilter(
|
|
args,
|
|
"owner-manager-email",
|
|
"Pass --owner-manager-email when using workloads-by-owner-manager-close-window."
|
|
);
|
|
effectiveArgs = applyCloseWindowIntentDefaults(args);
|
|
}
|
|
|
|
if (intent === "workloads-by-resource-close-window") {
|
|
const payload = await executeWorkloadResourceIntent({
|
|
args: effectiveArgs,
|
|
intent,
|
|
limit,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
});
|
|
const json = await emitJson(effectiveArgs, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
|
|
if (intent === "workloads-by-manager-resource-close-window") {
|
|
const payload = await executeWorkloadManagerResourceIntent({
|
|
args: effectiveArgs,
|
|
intent,
|
|
limit,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
});
|
|
const json = await emitJson(effectiveArgs, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
|
|
if (intent === "opportunities-current-team-close-window") {
|
|
const payload = await executeOpportunityCoverageIntent({
|
|
args: effectiveArgs,
|
|
intent,
|
|
resolvedView,
|
|
limit,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
coverageSqlFactory: (opportunityIds) => buildCurrentTeamCoverageSqlForOpportunityIds(opportunityIds),
|
|
coverageScope: SCOPE_MY_TEAM,
|
|
matchStrategy:
|
|
"Intersect the close-date opportunity window with the current-session SA Attach presales coverage rows. This is the canonical fallback for asks about the manager of allocated resources, people attached through SR work, or similar current-team involvement phrasing.",
|
|
coverageRequestKey: "currentTeamCoverage",
|
|
allowCommercialFallback: true,
|
|
extraWarnings: [
|
|
"This filter does not rely on a direct 'resource manager' column. It uses the current session team coverage extract, which is the closest stable signal exposed by the workbook for manager/resource/SR involvement asks.",
|
|
],
|
|
});
|
|
const json = await emitJson(effectiveArgs, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
|
|
if (intent === "opportunities-by-resource-close-window") {
|
|
const resourceFilter = resolveResourceFilter(effectiveArgs);
|
|
const payload = await executeOpportunityCoverageIntent({
|
|
args: effectiveArgs,
|
|
intent,
|
|
resolvedView,
|
|
limit,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
coverageSqlFactory: (opportunityIds) =>
|
|
buildExplicitCoverageSqlForOpportunityIds(opportunityIds, resourceFilter.resourceEmailSql),
|
|
coverageScope: SCOPE_ORG_WIDE,
|
|
matchStrategy:
|
|
"Intersect the close-date opportunity window with SA Attach presales coverage rows where the explicit resource email appears in the coverage hierarchy.",
|
|
requestScope: {
|
|
resource: resourceFilter.resourceInput,
|
|
resourceEmail: resourceFilter.resourceEmail,
|
|
},
|
|
coverageRequestKey: "matchedCoverage",
|
|
allowCommercialFallback: true,
|
|
extraWarnings: [
|
|
"This filter uses SA Attach presales coverage hierarchy rows, which is the closest stable signal for explicit resource-scoped opportunity coverage.",
|
|
],
|
|
});
|
|
const json = await emitJson(effectiveArgs, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
|
|
if (intent === "opportunities-by-manager-close-window") {
|
|
const managerFilter = resolveManagerEmailFilter(effectiveArgs);
|
|
const payload = await executeOpportunityCoverageIntent({
|
|
args: effectiveArgs,
|
|
intent,
|
|
resolvedView,
|
|
limit,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
coverageSqlFactory: (opportunityIds) =>
|
|
buildExplicitCoverageSqlForOpportunityIds(opportunityIds, managerFilter.managerEmailSql),
|
|
coverageScope: SCOPE_ORG_WIDE,
|
|
matchStrategy:
|
|
"Intersect the close-date opportunity window with SA Attach presales coverage rows where the explicit manager email appears in the hierarchy.",
|
|
requestScope: {
|
|
managerEmail: managerFilter.managerEmail,
|
|
},
|
|
coverageRequestKey: "matchedCoverage",
|
|
allowCommercialFallback: true,
|
|
extraWarnings: [
|
|
"This filter uses SA Attach presales coverage hierarchy rows, which is the closest stable signal for explicit manager-scoped opportunity coverage.",
|
|
],
|
|
});
|
|
const json = await emitJson(effectiveArgs, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
|
|
const definition = VIEW_DEFS[resolvedView];
|
|
if (!definition) {
|
|
const suggestion = suggestViewName(resolvedView);
|
|
throw new Error(
|
|
suggestion
|
|
? `Unknown view "${resolvedView}". Did you mean "${suggestion}"?`
|
|
: `Unknown view "${resolvedView}". Supported views: ${viewNames().join(", ")}`
|
|
);
|
|
}
|
|
const effectiveScope = resolveEffectiveScope({
|
|
requestedScope,
|
|
viewName: resolvedView,
|
|
definition,
|
|
});
|
|
|
|
if (resolvedView === "current-fiscal-year") {
|
|
const [result] = await executeSqlQueries({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
queries: [{ sql: definition.buildSql(), limit: 1 }],
|
|
});
|
|
const scope = buildViewScopeDescriptor(resolvedView, definition, {
|
|
requestedScope,
|
|
effectiveScope,
|
|
});
|
|
const payload = {
|
|
generatedAt: new Date().toISOString(),
|
|
scope,
|
|
apiMetrics: buildApiMetrics([
|
|
{
|
|
label: resolvedView,
|
|
result,
|
|
source: definition.primarySource || SOURCE_DV_SE_TEAM,
|
|
},
|
|
]),
|
|
request: {
|
|
mode: "view",
|
|
view: resolvedView,
|
|
...(intent ? { intent } : {}),
|
|
endpoint: new URL(SQL_ENDPOINT, baseUrl).toString(),
|
|
statePath,
|
|
metaPath,
|
|
requestedScope,
|
|
scopeMode: scope.scopeMode,
|
|
useCache,
|
|
},
|
|
columns: definition.fields,
|
|
records: mapRows(definition.fields, result?.aResults || []),
|
|
warnings: buildWarnings({
|
|
requestedLimit: 1,
|
|
recordsCount: result?.aResults?.length || 0,
|
|
customSql: false,
|
|
}),
|
|
raw: result,
|
|
};
|
|
const json = await emitJson(effectiveArgs, payload, runtimeHome);
|
|
console.log(json);
|
|
return;
|
|
}
|
|
|
|
const needsFiscalYear = definition.needsFiscalYear !== false;
|
|
let fiscalYear = null;
|
|
let fiscalYearSource = null;
|
|
let fiscalYearApiMetrics = buildApiMetrics();
|
|
const isSaAttachCommercialView =
|
|
resolvedView.startsWith("sa-attach-") &&
|
|
!["sa-attach-hours-by-opportunity", "sa-attach-presales-coverage"].includes(resolvedView);
|
|
const dateWindow =
|
|
resolvedView === "opportunities-close-window" || (isSaAttachCommercialView && hasAnyDateWindowArgs(effectiveArgs))
|
|
? resolveDateWindow(effectiveArgs, { from: "today", to: "+7d" })
|
|
: null;
|
|
const shouldResolveFiscalYear =
|
|
needsFiscalYear && !(isSaAttachCommercialView && dateWindow);
|
|
if (shouldResolveFiscalYear) {
|
|
fiscalYearSource = effectiveArgs["fiscal-year"]
|
|
? "explicit"
|
|
: hasFreshFiscalYearCache(meta)
|
|
? "session-meta-cache"
|
|
: "live-query";
|
|
const fiscalYearResolution = await resolveFiscalYear({
|
|
args: effectiveArgs,
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
autoAuthRefresh: true,
|
|
includeApiMetrics: true,
|
|
});
|
|
fiscalYear = fiscalYearResolution.fiscalYear;
|
|
fiscalYearApiMetrics = fiscalYearResolution.apiMetrics;
|
|
if (fiscalYearSource === "live-query") {
|
|
await persistResolvedFiscalYear(metaPath, meta, fiscalYear);
|
|
}
|
|
} else if (dateWindow?.fiscalYear) {
|
|
fiscalYear = dateWindow.fiscalYear;
|
|
fiscalYearSource = "derived-from-date-window";
|
|
}
|
|
|
|
const statuses = splitCsv(effectiveArgs["opty-status"], definition.defaultStatuses);
|
|
const resourceFilter =
|
|
resolvedView === "srs-by-resource-email"
|
|
? resolveResourceFilter(effectiveArgs)
|
|
: null;
|
|
const saAttachFilters =
|
|
isSaAttachCommercialView
|
|
? resolveSaAttachFilters(effectiveArgs, definition)
|
|
: null;
|
|
const explicitCluster =
|
|
resolvedView === "opportunities-close-window"
|
|
? resolveOptionalClusterFilter(effectiveArgs)
|
|
: null;
|
|
const primarySql = definition.buildSql({
|
|
fiscalYear,
|
|
previousFiscalYear: previousFiscalYear(fiscalYear),
|
|
statuses,
|
|
scope: effectiveScope,
|
|
...dateWindow,
|
|
...resourceFilter,
|
|
...(explicitCluster ? { cluster: explicitCluster } : {}),
|
|
...(effectiveArgs.ownerEmail ? { ownerEmail: effectiveArgs.ownerEmail } : {}),
|
|
...(effectiveArgs.ownerManagerEmail ? { ownerManagerEmail: effectiveArgs.ownerManagerEmail } : {}),
|
|
...(saAttachFilters ? { saAttachFilters } : {}),
|
|
});
|
|
const fallbackSql = allowFallbackForRequest(definition, { intent, view: resolvedView })
|
|
&& definition.buildFallbackSql
|
|
? definition.buildFallbackSql({
|
|
fiscalYear,
|
|
previousFiscalYear: previousFiscalYear(fiscalYear),
|
|
statuses,
|
|
scope: SCOPE_MY_TEAM,
|
|
...dateWindow,
|
|
...resourceFilter,
|
|
...(saAttachFilters ? { saAttachFilters } : {}),
|
|
})
|
|
: null;
|
|
const effectiveLimit = Number.isFinite(limit) ? limit : definition.defaultLimit;
|
|
const execution = await executeQueryWithFallback({
|
|
state,
|
|
meta,
|
|
baseUrl,
|
|
statePath,
|
|
metaPath,
|
|
useCache,
|
|
primarySql,
|
|
fallbackSql,
|
|
limit: effectiveLimit,
|
|
primarySource: definition.primarySource,
|
|
fallbackSource: definition.fallbackSource,
|
|
});
|
|
|
|
const scope = buildViewScopeDescriptor(resolvedView, definition, {
|
|
source: execution.source,
|
|
fallbackUsed: execution.fallbackUsed,
|
|
requestedScope,
|
|
effectiveScope: execution.fallbackUsed ? SCOPE_MY_TEAM : effectiveScope,
|
|
});
|
|
const payload = {
|
|
generatedAt: new Date().toISOString(),
|
|
scope,
|
|
apiMetrics: buildApiMetrics([
|
|
...fiscalYearApiMetrics.queries,
|
|
...execution.apiQueries.map((entry) => ({
|
|
...entry,
|
|
label: resolvedView,
|
|
})),
|
|
]),
|
|
request: {
|
|
mode: "view",
|
|
view: resolvedView,
|
|
...(intent ? { intent } : {}),
|
|
endpoint: new URL(SQL_ENDPOINT, baseUrl).toString(),
|
|
statePath,
|
|
metaPath,
|
|
requestedScope,
|
|
scopeMode: scope.scopeMode,
|
|
...(execution.source ? { dataSource: execution.source } : {}),
|
|
...(execution.fallbackUsed ? { fallbackUsed: true } : {}),
|
|
...(fiscalYear ? { fiscalYear, fiscalYearSource } : {}),
|
|
...(dateWindow
|
|
? {
|
|
datePreset: dateWindow.preset,
|
|
fromDate: dateWindow.fromDate,
|
|
fromDateInput: dateWindow.fromDateInput,
|
|
toDate: dateWindow.toDate,
|
|
toDateInput: dateWindow.toDateInput,
|
|
...(dateWindow.fiscalQuarter
|
|
? {
|
|
fiscalYear: dateWindow.fiscalYear,
|
|
fiscalQuarter: dateWindow.fiscalQuarter,
|
|
}
|
|
: {}),
|
|
...(dateWindow.week
|
|
? {
|
|
week: dateWindow.week,
|
|
weekLabel: dateWindow.weekLabel,
|
|
weekYear: dateWindow.weekYear,
|
|
}
|
|
: {}),
|
|
}
|
|
: {}),
|
|
...(explicitCluster ? { cluster: explicitCluster } : {}),
|
|
...(resourceFilter
|
|
? {
|
|
resourceInput: resourceFilter.resourceInput,
|
|
resourceEmail: resourceFilter.resourceEmail,
|
|
matchRole: resourceFilter.matchRole,
|
|
srStatuses: resourceFilter.srStatuses,
|
|
}
|
|
: {}),
|
|
...(saAttachFilters
|
|
? {
|
|
cluster: saAttachFilters.cluster,
|
|
solutions: saAttachFilters.solutions,
|
|
revenueLineStatuses: saAttachFilters.statuses,
|
|
executiveProductLobs: saAttachFilters.productLobs,
|
|
revenueTypeGroups: saAttachFilters.revenueTypeGroups,
|
|
salesCreditType: saAttachFilters.salesCreditType,
|
|
minOpportunityProbability: saAttachFilters.minOpportunityProbability,
|
|
...(saAttachFilters.forecastTypes?.length > 0
|
|
? { forecastTypes: saAttachFilters.forecastTypes }
|
|
: {}),
|
|
...(saAttachFilters.ownerEmail ? { ownerEmail: saAttachFilters.ownerEmail } : {}),
|
|
...(saAttachFilters.ownerManagerEmail
|
|
? { ownerManagerEmail: saAttachFilters.ownerManagerEmail }
|
|
: {}),
|
|
}
|
|
: {}),
|
|
statuses,
|
|
limit: effectiveLimit,
|
|
useCache,
|
|
sql: execution.sql,
|
|
...(execution.primaryError ? { primarySqlError: execution.primaryError } : {}),
|
|
},
|
|
columns: definition.fields,
|
|
records: mapRows(definition.fields, execution.result?.aResults || []),
|
|
warnings: buildWarnings({
|
|
requestedLimit: effectiveLimit,
|
|
recordsCount: execution.result?.aResults?.length || 0,
|
|
customSql: false,
|
|
}).concat(execution.warnings),
|
|
raw: execution.result,
|
|
};
|
|
|
|
const json = await emitJson(effectiveArgs, payload, runtimeHome);
|
|
console.log(json);
|
|
}
|
|
|
|
export {
|
|
applyCloseWindowIntentDefaults,
|
|
applyCurrentWorkloadIntentDefaults,
|
|
buildManagerResourceWorkloadSql,
|
|
buildPrimaryOpportunityWhereClauses,
|
|
buildSaAttachOpportunityWhereClauses,
|
|
buildWorkloadCloseWindowSql,
|
|
buildApiMetrics,
|
|
buildIntentScopeDescriptor,
|
|
buildScopeDescriptor,
|
|
buildViewScopeDescriptor,
|
|
hasAnyDateWindowArgs,
|
|
renderViewSql,
|
|
resolveDateWindow,
|
|
resolveCoverageLimit,
|
|
resolveManagerEmailListFilter,
|
|
resolveQuarterInput,
|
|
resolveSaAttachFilters,
|
|
summarizeOrgWideRevenueLineCoverage,
|
|
summarizeCurrentTeamOpportunityCoverage,
|
|
withConsoleLogRoutedToStderr,
|
|
};
|
|
|
|
const isDirectExecution =
|
|
process.argv[1] && path.resolve(process.argv[1]) === fileURLToPath(import.meta.url);
|
|
|
|
if (isDirectExecution) {
|
|
main().catch((error) => {
|
|
console.error(error.message);
|
|
process.exitCode = 1;
|
|
});
|
|
}
|