Fusion CRM OTBI: BI function "MonthName"Returns the Month Number ( 01...12) Instead Of the Name (Doc ID 1992357.1)

Last updated on SEPTEMBER 21, 2016

Applies to:

Oracle Fusion Sales Cloud Service - Version 11.1.8.0.0 and later
Oracle Fusion Application Toolkit Cloud Service - Version 11.1.4.0.0 and later
Oracle Fusion Application Toolkit - Version 11.1.1.5.1 and later
Information in this document applies to any platform.

Symptoms

Fusion Applications 11.1.8 CRM Cloud OBIEE - custom analysis definition using OBIEE formula  MonthName returns number such as 01 thru to 12


Formula such as
     MonthName("Sales CRM - Pipeline"."Revenue Extension"."Extension Attribute Timestamp 001")

logical SQL

set variable LOGLEVEL = 2;SELECT
   0 s_0,
   MonthName("Sales - CRM Pipeline"."Revenue Extension"."Extension Attribute Timestamp 001") s_1
FROM "Sales - CRM Pipeline"
ORDER BY 1, 2 ASC NULLS LAST
FETCH FIRST 75001 ROWS ONLY

physical

WITH
SAWITH0 AS (select T231364.C208342574 as c1
from
     (SELECT V53960257.EXTN_ATTRIBUTE_TIMESTAMP001 AS C208342574,         V53960257.R_REVN_ID AS PKA_RevenueRevnId0 FROM (SELECT Revenue.REVN_ID AS R_REVN_ID,         Revenue.EXTN_ATTRIBUTE_TIMESTAMP001 FROM MOO_REVN Revenue, MOO_OPTY OpportunitiesPEO WHERE (Revenue.OPTY_ID = OpportunitiesPEO.OPTY_ID) AND ( (((   OpportunitiesPEO.opty_id IN (SELECT opty_id FROM moo_revn_partner)  ) OR ( OpportunitiesPEO.opty_id IN
(SELECT DISTINCT myopres.opty_id
FROM Moo_opty_resources myopres
WHERE myopres.resource_id =
  (SELECT HZ_SESSION_UTIL.GET_USER_PARTYID FROM dual
  )
AND myopres.access_level_code IN ('100', '200', '300')
)   ) OR ( 1 = 1 ) OR ( 1 = 1 )))  AND ( ((  ( (Revenue.REVN_LINE_TYPE_CODE = 'LINE_SET_MEMBER_REVN' ) )  OR ( (Revenue.REVN_LINE_TYPE_CODE = 'STANDARD_REVN' ) )  OR ( (Revenue.REVN_LINE_TYPE_CODE = 'OPTY_SUMMARY_REVN' ) )  )) ) )) V53960257) T231364),
SAWITH1 AS (select RTRIM(TO_CHAR(D1.c1, 'Mon')) as c1
from
     SAWITH0 D1)
select D1.c1 as c1, D1.c2 as c2 from ( select distinct 0 as c1,
     D1.c1 as c2
from
     SAWITH1 D1
order by c2 ) D1 where rownum
return month number instead of month name.

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