My Oracle Support Banner

BIM Lead Activity Report Fails with Invalid Column Index Error (Doc ID 340697.1)

Last updated on DECEMBER 05, 2019

Applies to:

Oracle Marketing Intelligence - Version 11.5.9 to 11.5.10.2 [Release 11.5 to 11.5.10]
Information in this document applies to any platform.

Symptoms

When logging in as Daily Marketing Intelligence responsibility and selecting  the Lead Activity report, the following error is displayed.

Invalid column index. Main query is: SELECT BIM_SALES_GROUP VIEWBY, prior_open BIM_MEASURE1,
leads_new BIM_MEASURE2, leads_converted BIM_MEASURE3, leads_dead BIM_MEASURE4, curr_open
BIM_MEASURE5, (curr_total-curr_leads_changed) BIM_MEASURE6, leads_closed BIM_MEASURE8,
DECODE(curr_open,0,0,((curr_total-curr_leads_changed)/curr_open)*100) BIM_MEASURE7,
SUM(prior_open) OVER() BIM_GRAND_TOTAL1, SUM(leads_new) OVER() BIM_GRAND_TOTAL2,
SUM(leads_converted) OVER() BIM_GRAND_TOTAL3, SUM(leads_dead) OVER() BIM_GRAND_TOTAL4,
SUM(curr_open) OVER() BIM_GRAND_TOTAL5, SUM(curr_total-curr_leads_changed) OVER()
BIM_GRAND_TOTAL6, DECODE(SUM(curr_open) OVER(),0,0,(SUM(curr_total-curr_leads_changed)
OVER()/SUM(curr_open) OVER())*100) BIM_GRAND_TOTAL7, SUM(leads_closed) OVER() BIM_GRAND_TOTAL8,
VIEWBYID FROM ( select BIM_SALES_GROUP, VIEWBYID, sum(prior_open) prior_open, sum(curr_open)
curr_open, sum(curr_total) curr_total, sum(leads_converted) leads_converted, sum(leads_new)
leads_new, sum(leads_dead) leads_dead, sum(leads_closed) leads_closed, sum(curr_leads_changed)
curr_leads_changed FROM ( select d.group_name BIM_SALES_GROUP, b.group_id VIEWBYID, SUM(case when
c.report_date=:b_bv1 - 1 then b.leads-(b.leads_closed+b.leads_dead+b.leads_converted) else 0 end)
prior_open, SUM(case when c.report_date=:b_bv2 then
b.leads-(b.leads_closed+b.leads_dead+b.leads_converted) else 0 end) curr_open, SUM(case when
c.report_date=:b_bv2 then b.leads else 0 end) curr_total, 0 leads_converted, 0 leads_new, 0
leads_dead, 0 leads_closed, SUM(case when c.report_date=:b_bv2 then b.leads_changed else 0 end)
curr_leads_changed FROM jtf_rs_grp_relations rel,bis_system_date bsd,BIM_I_LD_GEN_SG_MV b,
FII_TIME_RPT_STRUCT c,jtf_rs_groups_tl d WHERE rel.relation_type='PARENT_GROUP' AND
(bsd.current_date_id BETWEEN rel.start_date_active AND
nvl(rel.end_date_active,bsd.current_date_id)) AND NVL(rel.delete_flag,'N')<>'Y' AND
rel.group_id<>rel.related_group_id AND rel.related_group_id in(:b_bv3) AND b.group_id=rel.group_id
AND b.group_id=d.group_id AND d.language=USERENV('LANG') AND c.calendar_id=-1 AND c.report_date in
(:b_bv2,:b_bv1 - 1) AND BITAND(c.record_type_id,1143)=c.record_type_id AND b.time_id=c.time_id AND
b.period_type_id=c.period_type_id AND b.resource_id = -1 GROUP BY d.group_name,b.group_id UNION
ALL /*Prior Open and Current Open for reps*/ SELECT a.source_name BIM_SALES_GROUP, b.group_id
VIEWBYID, SUM(case when c.report_date=:b_bv1 - 1 then
b.leads-(b.leads_closed+b.leads_dead+b.leads_converted) else 0 end) prior_open, SUM(case when
c.report_date=:b_bv2 then b.leads-(b.leads_closed+b.leads_dead+b.leads_converted) else 0 end)
curr_open, SUM(case when c.report_date=:b_bv2 then b.leads else 0 end) curr_total, 0
leads_converted, 0 leads_new, 0 leads_dead, 0 leads_closed, SUM(case when c.report_date=:b_bv2
then b.leads_changed else 0 end) curr_leads_changed FROM JTF_RS_RESOURCE_EXTNS_VL
a,BIM_I_LD_GEN_SG_MV b,FII_TIME_RPT_STRUCT c WHERE a.resource_id=b.resource_id AND b.group_id
in(:b_bv3) AND c.calendar_id=-1 AND c.report_date in (:b_bv2,:b_bv1 - 1) AND
BITAND(c.record_type_id,1143)=c.record_type_id AND b.time_id=c.time_id AND
b.period_type_id=c.period_type_id AND b.resource_id <> -1 GROUP BY a.source_name,b.group_id UNION
ALL /*Others for sales group*/ select d.group_name BIM_SALES_GROUP, b.group_id VIEWBYID, 0
prior_open, 0 curr_open, 0 curr_total, sum(leads_converted) leads_converted, sum(leads_new)
leads_new, sum(leads_dead) leads_dead, sum(leads_closed) leads_closed, 0 curr_leads_changed FROM
jtf_rs_grp_relations rel,bis_system_date bsd,BIM_I_LD_GEN_SG_MV b, FII_TIME_RPT_STRUCT
c,jtf_rs_groups_tl d WHERE rel.relation_type='PARENT_GROUP' AND (bsd.current_date_id BETWEEN
rel.start_date_active AND nvl(rel.end_date_active,bsd.current_date_id)) AND
NVL(rel.delete_flag,'N')<>'Y' AND rel.group_id<>rel.related_group_id AND rel.related_group_id
in(:b_bv3) AND b.group_id=rel.group_id AND b.group_id=d.group_id AND d.language=USERENV('LANG')
AND c.calendar_id=-1 AND c.report_date = :b_bv2 AND
BITAND(c.record_type_id,:l_record_type)=c.record_type_id AND b.time_id=c.time_id AND
b.period_type_id=c.period_type_id AND b.resource_id = -1 GROUP BY d.group_name,b.group_id UNION
ALL /*Others for reps*/ SELECT a.source_name BIM_SALES_GROUP, b.group_id VIEWBYID, 0 prior_open, 0
curr_open, 0 curr_total, sum(b.leads_converted) leads_converted, sum(b.leads_new) leads_new,
sum(b.leads_dead) leads_dead, sum(b.leads_closed) leads_closed, 0 curr_leads_changed FROM
JTF_RS_RESOURCE_EXTNS_VL a,BIM_I_LD_GEN_SG_MV b,FII_TIME_RPT_STRUCT c WHERE
a.resource_id=b.resource_id AND b.group_id in(:b_bv3) AND c.calendar_id=-1 AND c.report_date =
:b_bv2 AND BITAND(c.record_type_id,:l_record_type)=c.record_type_id AND b.time_id=c.time_id AND
b.period_type_id=c.period_type_id AND b.resource_id <> -1 GROUP BY a.source_name,b.group_id )
GROUP BY BIM_SALES_GROUP,VIEWBYID HAVING sum(prior_open) > 0 OR sum(leads_new) > 0 OR
sum(leads_converted) > 0 OR sum(leads_dead) > 0 OR sum(curr_open) > 0 OR
sum(curr_total)-sum(curr_leads_changed) > 0 ) ORDER BY NLSSORT(VIEWBY,'NLS_SORT=BINARY') ASC

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.