AnsweredAssumed Answered

Making an element a lookup in a portal.

Question asked by cupjohn on Nov 24, 2014
Latest reply on Dec 17, 2014 by navzjoshi00

I am trying to make a field that I have added to a query into a lookup field in a portlet.  The element is in row 4 - cost_type.  I don't know how to set up the query and then the portlet to make it as a lookup for the user.  Any help?

 

Chad

 

SELECT 
       @SELECT:DIM:USER_DEF:IMPLIED:ACTUALS:t.prProjectID||t.prid||a.prID||r.unique_name:dim@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:t.prProjectID:PROJECT_ID@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:t.cost_type:cost_type@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:I.ID:CL_PROJECT_ID@, 
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:I.CODE:PROJECT_CODE@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:I.Name:PRJ_NAME@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:t.prID:TASK_ID@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:a.prID:ASSIGNMENT_ID@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:t1.tasktypelookup:task_type_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:l.name:task_type@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:t.prname:task_name@, 
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:t.prUserText1:chrgcode_comme@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:ch.prname:chrge_code@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:ch.prid:chrge_code_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:r.id:p_res_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:r.unique_name:res_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:r.full_name:res_fullname@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:ACTUALS:SUM(NVL(CASE WHEN sr.request_name = 'DAILYRESOURCEACTCURVE' THEN
          (CASE WHEN r.resource_type = 0 THEN s.slice ELSE 0 END) ELSE 0 END,0)):ACTUAL_L_HOURS@
      




FROM   prTask t
       INNER JOIN prAssignment a ON t.prID = a.prTaskID
       
       INNER JOIN srm_resources r ON a.prResourceID = r.id
       INNER JOIN prj_blb_slices s ON a.prID = s.prj_object_id
       INNER JOIN prj_blb_slicerequests sr ON s.slice_request_id = sr.id
       left join odf_ca_task t1 on t1.id=t.prid
       left join PAC_MNT_PROJECTS t2 on t2.id=t.prid
       left join inv_investments I on I.id=t.prprojectid 
       left join prchargecode CH on ch.prid=t.prchargecodeid
       left join cmn_lookups_v  l on l.lookup_type='COMDATA TASK TYPE' and language_code='en' and l.lookup_code=t1.tasktypelookup
       left join cmn_lookups_v  l2 on l2.lookup_type='LOOKUP_FIN_COSTTYPECODE' and language_code='en' and l2.lookup_code=t2.COST_TYPE
WHERE 
 @FILTER@
AND (sr.request_name IN ('DAILYRESOURCEACTCURVE'))
AND   (r.resource_type <= 1)
AND s.slice_date >= @WHERE:PARAM:USER_DEF:DATE:p_startDate@ 
AND s.slice_date <= ( case when trunc(cast(@WHERE:PARAM:USER_DEF:DATE:p_endDate@ as date) - cast(@WHERE:PARAM:USER_DEF:DATE:p_startDate@ as date) ) > 730 
                          then add_months(@WHERE:PARAM:USER_DEF:DATE:p_startDate@,+24)  else @WHERE:PARAM:USER_DEF:DATE:p_endDate@ end)


GROUP BY t.prProjectID ,
       t.cost_type,
       I.ID , 
       I.CODE ,
       I.Name ,
       t.prID ,       
       t1.tasktypelookup ,
       t.prname , 
       ch.prname ,
       r.unique_name ,
       r.full_name 
    ,a.prID,t1.obj_cai_task_type ,
       l.name ,r.id,ch.prid,t.prUserText1

Outcomes