Oracle Sales Cloud Analytics: Lead Reports Does Not Show The Data Correctly

(Doc ID 2400792.1)

Last updated on MAY 18, 2018

Applies to:

Oracle Fusion Sales Cloud Service - Version 11.13.18.02.0 and later
Information in this document applies to any platform.

Symptoms

Custom report is showing wrong data in report. The analysis supposed to show Contact and Leads information, but it is showing more rows than he can see in UI and some Lead Columns are blank.

SQL:

SELECT
  "Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute Character 005" s_1,
  "Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute Character 035" s_2,
  "Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute LOV 054" s_3,
  "Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute Number 004" s_4,
  "Sales - CRM Pipeline"."Customer"."Customer City" s_5,
  "Sales - CRM Pipeline"."Customer"."Customer Country" s_6,
  "Sales - CRM Pipeline"."Customer"."Customer Email Address" s_7,
  "Sales - CRM Pipeline"."Customer"."Customer Unique Name" s_8,
  "Sales - CRM Pipeline"."Customer"."Last Name" s_9,
  "Sales - CRM Pipeline"."Customer"."Primary Contact Phone Number" s_10,
  "Sales - CRM Pipeline"."Customer"."Province" s_11,
  "Sales - CRM Pipeline"."Employee"."Employee Login Name" s_12,
  "Sales - CRM Pipeline"."Lead Extension"."Extension Attribute Character 017" s_13,
  "Sales - CRM Pipeline"."Lead Extension"."Extension Attribute Character 019" s_14,
  "Sales - CRM Pipeline"."Lead Extension"."Extension Attribute Character 020" s_15,
  "Sales - CRM Pipeline"."Lead"."Lead Creation Date" s_16,
  "Sales - CRM Pipeline"."Lead"."Lead Number" s_17,
  "Sales - CRM Pipeline"."Lead"."Lead Status" s_18,
  "Sales - CRM Pipeline"."Product Group Extension"."Extension Attribute LOV 043" s_19,
  "Sales - CRM Pipeline"."Product"."Product Name" s_20,
  DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute LOV 054") s_21,
  DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Customer"."Customer Country") s_22,
  DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Lead"."Lead Status") s_23,
  DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Product Group Extension"."Extension Attribute LOV 043") s_24,
  SORTKEY("Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute LOV 054") s_25,
  SORTKEY("Sales - CRM Pipeline"."Lead"."Lead Status") s_26,
  SORTKEY("Sales - CRM Pipeline"."Product Group Extension"."Extension Attribute LOV 043") s_27,
  "Sales - CRM Pipeline"."Lead Facts"."# of Leads" s_28,
  "Sales - CRM Pipeline"."Pipeline Detail Facts"."# of Products" s_29,
  REPORT_AGGREGATE("Sales - CRM Pipeline"."Lead Facts"."# of Leads" BY "Sales - CRM Pipeline"."Product"."Product Name",DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Product Group Extension"."Extension Attribute LOV 043"),DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Lead"."Lead Status"),"Sales - CRM Pipeline"."Lead"."Lead Creation Date","Sales - CRM Pipeline"."Lead"."Lead Number","Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute Character 005",DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute LOV 054"),"Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute Character 035","Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute Number 004","Sales - CRM Pipeline"."Customer"."Customer Email Address","Sales - CRM Pipeline"."Customer"."Customer Unique Name","Sales - CRM Pipeline"."Customer"."Last Name",DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Customer"."Customer Country"),"Sales - CRM Pipeline"."Employee"."Employee Login Name","Sales - CRM Pipeline"."Lead Extension"."Extension Attribute Character 017","Sales - CRM Pipeline"."Lead Extension"."Extension Attribute Character 019","Sales - CRM Pipeline"."Lead Extension"."Extension Attribute Character 020","Sales - CRM Pipeline"."Customer"."Primary Contact Phone Number") s_30,
  REPORT_AGGREGATE("Sales - CRM Pipeline"."Pipeline Detail Facts"."# of Products" BY "Sales - CRM Pipeline"."Product"."Product Name",DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Product Group Extension"."Extension Attribute LOV 043"),DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Lead"."Lead Status"),"Sales - CRM Pipeline"."Lead"."Lead Creation Date","Sales - CRM Pipeline"."Lead"."Lead Number","Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute Character 005",DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute LOV 054"),"Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute Character 035","Sales - CRM Pipeline"."Customer Person Extension"."Extension Attribute Number 004","Sales - CRM Pipeline"."Customer"."Customer Email Address","Sales - CRM Pipeline"."Customer"."Customer Unique Name","Sales - CRM Pipeline"."Customer"."Last Name",DESCRIPTOR_IDOF("Sales - CRM Pipeline"."Customer"."Customer Country"),"Sales - CRM Pipeline"."Employee"."Employee Login Name","Sales - CRM Pipeline"."Lead Extension"."Extension Attribute Character 017","Sales - CRM Pipeline"."Lead Extension"."Extension Attribute Character 019","Sales - CRM Pipeline"."Lead Extension"."Extension Attribute Character 020","Sales - CRM Pipeline"."Customer"."Primary Contact Phone Number") s_31
FROM "Sales - CRM Pipeline"
WHERE
("Customer Person Extension"."Extension Attribute Character 035" IN ( '42271916','42419844','42082708','41531604','42719455','44603910','44540386','25759883','41252962'))
ORDER BY 1, 21 ASC NULLS LAST, 9 ASC NULLS LAST, 2 ASC NULLS LAST, 10 ASC NULLS LAST, 26 ASC NULLS LAST, 4 ASC NULLS LAST, 22 ASC NULLS LAST, 3 ASC NULLS LAST, 8 ASC NULLS LAST, 11 ASC NULLS LAST, 13 ASC NULLS LAST, 27 ASC NULLS LAST, 19 ASC NULLS LAST, 24 ASC NULLS LAST, 17 ASC NULLS LAST, 15 ASC NULLS LAST, 14 ASC NULLS LAST, 16 ASC NULLS LAST, 18 ASC NULLS LAST, 28 ASC NULLS LAST, 20 ASC NULLS LAST, 25 ASC NULLS LAST, 5 ASC NULLS LAST, 7 ASC NULLS LAST, 23 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

 

Changes

 

Cause

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms