AnsweredAssumed Answered

Portlet with MVL

Question asked by FlavioSeixas_Deloitte on Feb 10, 2011
Latest reply on Jul 9, 2015 by Robert Ensinger
Hello Everyone,

I need a Idea portlet that shows some multivalued lookups in a single line delimited by comma, so I'm using the FED_FLATTEN_MVLOOKUPS_FCT function. But, this function returns the id, how can I convert the id to the value?
SELECT   @SELECT:DIM:USER_DEF:IMPLIED:INV:I.ID:ID@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:I.CODE:CODE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:I.NAME:ASSUNTO@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:I.description:DESCRIPTION@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:N.INITIATOR_ID:REQUESTOR_ID@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:C.RIO_IMPACT:IMPACT@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:C.RIO_URGENCY:URGENCY@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:C.OBJ_OBJECTIVE:OBJECTIVE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:C.OBJ_REQUEST_TYPE:TYPE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:I.SCHEDULE_START:START_DATE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:I.SCHEDULE_FINISH:FINISH_DATE@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:L.LOOKUP_CODE:STATUS@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:R.FIRST_NAME || ' ' || R.LAST_NAME:REQUESTOR_NAME@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:C.RIO_IMPACT * C.RIO_URGENCY:PRIORITY@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:FED_FLATTEN_MVLOOKUPS_FCT('idea', I.ID, 'rio_target_fa', ','):TARGET_FA@,
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:INV:FED_FLATTEN_MVLOOKUPS_FCT('idea', I.ID, 'rio_target_manager', ','):TARGET_MANAGER@

FROM     
INV_INVESTMENTS I 
JOIN ODF_CA_IDEA C 
ON C.ID = I.ID
JOIN INV_IDEAS N
ON N.ID = I.ID
JOIN srm_resources R
ON R.USER_ID = N.INITIATOR_ID
LEFT JOIN CMN_LOOKUPS L
ON L.ID = I.STATUS

WHERE  I.odf_object_code = 'idea'
AND @FILTER@
Clarity Version/Build -12.1.0.5840
BD Oracle

Thanks in advance

Outcomes