search cancel

Dynamic Query Lookup Does Not Display Correct Values In DWH (Oracle)

book

Article ID: 255061

calendar_today

Updated On:

Products

Clarity PPM SaaS

Issue/Introduction

Clarity attribute with dynamic lookup value set does not match Clarity UI

Steps To Reproduce:

### A. Create 3 static lookups with values

-----------------------------------1
test(Site Funding Source)
Lookup ID=DIS_FUND_SRC
Hidden Key = LOOKUP_ENUM

Display Name=NAME

 

Values:

 Internal TD, id=1

 External,  id=3

-----------------------------------2
test(Site)
Lookup ID=DIS_SITE

Hidden Key = LOOKUP_ENUM

Display Name=NAME

 

Values:

ABD, id=1

XYZ Region,id=2

-----------------------------------3
test(Site Group)
Lookup ID=DIS_SITE_GROUP

Hidden Key = LOOKUP_ENUM

Display Name=NAME

 

Values:

TBD; id=0
DSE; id=1
XYZ Region;id=2

###  B. Create 1st custom object with attributes

1.

Object(Site Group Mapping)
id=dis_site_grp_mapping
Master

Event Enabled,Copy Enabled,Export Enabled,View All Enabled

 

with attributes:

a. 
test(Funding Source)
id=dis_fund_src

Lookup - Number

Lookup=test(Site Funding Source)


Value Required=yes

 

b. 
test(Site)
id=dis_site
data type=Lookup - Number

Lookup=test(Site)

Value Required=yes

c. 
test(Site Group)
id=dis_site_grp
 data type=Lookup - Number
Lookup=test(Site Group)

Value Required=yes

default=TBD

### C. Create 2 dynamic lookups

-----------------------------------1
test(Site Group Query)
id=DIS_SITE_GRP_QUERY

Query=

SELECT
@SELECT:MAIN.UNIQUE_CODE:[email protected],
@SELECT:MAIN.LOOKUP_NAME:[email protected],
@SELECT:MAIN.LANGUAGE_CODE:[email protected],
@SELECT:MAIN.LANGUAGE_ID:[email protected],
@SELECT:MAIN.LAST_UPDATED_DATE:[email protected],
@SELECT:MAIN.LOOKUP_TYPE:[email protected]
FROM
(SELECT
UNIQUE_CODE,
LOOKUP_NAME,
LANGUAGE_CODE,
LANGUAGE_ID,
LAST_UPDATED_DATE,
LOOKUP_TYPE
FROM
(SELECT SG.LOOKUP_CODE                                           UNIQUE_CODE,
        SG.NAME                                                   LOOKUP_NAME,
        SG.LANGUAGE_CODE                                          LANGUAGE_CODE,
        1                                                         LANGUAGE_ID,
        SG.LAST_UPDATED_DATE                                      LAST_UPDATED_DATE,
        SG.LOOKUP_TYPE                                            LOOKUP_TYPE

    FROM
        CMN_LOOKUPS_V SG
    WHERE
        SG.LANGUAGE_CODE      = 'en'
        AND SG.LOOKUP_TYPE LIKE 'DIS_SITE_GROUP'
AND SG.IS_ACTIVE = 1
AND SG.LOOKUP_CODE  = (SELECT NVL(SGMAP.DIS_SITE_GRP,0) AS SITE_GROUP
                                 FROM ODF_CA_DIS_SITE_GRP_MAPPING SGMAP
                                WHERE SGMAP.DIS_SITE = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
                                  AND SGMAP.DIS_FUND_SRC = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
                               FETCH FIRST 1 ROWS ONLY)) SUB) MAIN
WHERE @[email protected]

 

Hidden Key=unique_code
Display attribute=lookup_name

-----------------------------------2

test(Site Group Mapping Instances)
id=OBJECT_LOOKUP_DIS_SITE_GRP_MAPPING

Query=

SELECT @SELECT:ID:[email protected],                 @SELECT:CODE:[email protected],                 @SELECT:NAME:[email protected]          FROM   ODF_CA_DIS_SITE_GRP_MAPPING          WHERE  @[email protected]

 

###   D. Create instances on Home > Custom Objects > Object(Site Group Mapping)  list

ABC1    
test(Site)=ABD
test(Site Group)=DSE
test(Funding Source)=Internal TD

ABC2    
test(Site)=ABD
test(Site Group)=XYZ Region
test(Funding Source)=External

 

### E. Create 2nd custom Object 

Object(Site Percentages)
id=dis_site_split
Subobject; Master Object=Investment

Event Enabled,Copy Enabled
API-enabled

include in DWH=yes;

with attributes:

a. 
test(Funding Source)
id=dis_fund_src
Lookup=test(Site Funding Source)
api=p_FundingSource

Value Required=yes

include in DWH=yes;

 

b.
test(Site)
id=dis_site
Lookup=test(Site)

api=p_Site

Value Required=yes

include in DWH=yes;

 

c. 
test(Site Group)
id=dis_site_grp
Lookup=test(Site Group Query)


Lookup Parameter Mappings
funding_source_id = dis_fund_src
site_id = dis_site


include in DWH=yes;

 

### F. Create instances on Object(Site Percentages) under Project > Properties 

--1
aaa1
test(Site)=ABD
test(Site Group)=XYZ Region
test(Funding Source)=External

 

--2

aaa2
test(Site)=ABD
test(Site Group)=DSE
test(Funding Source)=Internal TD

 

### G. Run Load DWH and Load DWH Access Rights job

### H. In Home > Advanced Reporting > Create Ad hoc View
Choose Investment Domain
Choose Fields(Project Name, ID, Site, Funding Source, Site Group)

Expected: The column test(Site Group) in the DWH should match that of project
Actual: The column test(Site Group) in the DWH does not get populated. It is blank.

 

Environment

Release : 15.9.3

Resolution

This was reported as DE67552 and it was determined that the issue is not with the DWH data warehouse, but with the test(Site Group Query).

After implementing the following steps, the captioning worked in the DWH.

1. Delete the attribute
2. Modify the lookup NSQL to be.

SELECT
@SELECT:MAIN.UNIQUE_CODE:[email protected],
@SELECT:MAIN.LOOKUP_NAME:[email protected],
@SELECT:MAIN.LANGUAGE_CODE:[email protected],
@SELECT:MAIN.LANGUAGE_ID:[email protected],
@SELECT:MAIN.LAST_UPDATED_DATE:[email protected],
@SELECT:MAIN.LOOKUP_TYPE:[email protected]
FROM
(SELECT
UNIQUE_CODE,
LOOKUP_NAME,
LANGUAGE_CODE,
LANGUAGE_ID,
LAST_UPDATED_DATE,
LOOKUP_TYPE
FROM
(SELECT SG.LOOKUP_CODE                                           UNIQUE_CODE,
        SG.NAME                                                   LOOKUP_NAME,
        SG.LANGUAGE_CODE                                          LANGUAGE_CODE,
        1                                                         LANGUAGE_ID,
        SG.LAST_UPDATED_DATE                                      LAST_UPDATED_DATE,
        SG.LOOKUP_TYPE                                            LOOKUP_TYPE

    FROM
        CMN_LOOKUPS_V SG
    WHERE
        SG.LANGUAGE_CODE      = 'en'
        AND SG.LOOKUP_TYPE LIKE 'DIS_SITE_GROUP'
AND SG.IS_ACTIVE = 1
AND SG.LOOKUP_CODE IN (SELECT NVL(SGMAP.DIS_SITE_GRP,0) AS SITE_GROUP
                                 FROM ODF_CA_DIS_SITE_GRP_MAPPING SGMAP
                                WHERE SGMAP.DIS_SITE = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
                                  AND SGMAP.DIS_FUND_SRC = @WHERE:PARAM:USER_DEF:INTEGER:[email protected]
                               )) SUB) MAIN
WHERE @[email protected]

3. Recreated the object attribute.
4. Enter in the object instance attribute. Save.
5. Run Load DWH job.
6. Rerun adhoc view.