Dynamic Query Lookup Does Not Display Correct Values In DWH (Oracle)
Article ID: 255061
Updated On:
Products
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.
Feedback
