AnsweredAssumed Answered

Parametrized lookup based on Project ID

Question asked by matej256 on Feb 13, 2018
Latest reply on Feb 14, 2018 by matej256

Hi All, 

 

I'm currently struggling with one basic thing regarding param lookup. Was not able to find any solution for this so far....
I created new attribute "Cost_Type2" (definition attached) in Cost Plan Detail object where only inv_code is available as reference to project. 
I took query from system lookup LOOKUP_FIN_COSTTYPE and have done just small adjustment (last 3 lines were added). In SQL developer when I put below query with project code instead of @WHERE clause, it works OK. When I used it in new custom attribute "Cost_Type2" I got no results....

 

SELECT @SELECT:DATA.ID:ID@
, @SELECT:DATA.NAME:NAME@
, @SELECT:DATA.LOOKUP_CODE:CODE@
, @SELECT:DATA.LOOKUP_CODE:UNIQUE_CODE@
,@SELECT:DATA.DESCRIPTION:DESCRIPTION@
,@SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@
,@SELECT:LANG.ID:LANGUAGE_ID@
,@SELECT:DATA.LAST_UPDATED_DATE:LAST_UPDATED_DATE@
FROM CMN_LOOKUPS_V DATA, CMN_LANGUAGES LANG, PAC_MNT_PROJECTS PAC
WHERE @FILTER@
AND DATA.LOOKUP_TYPE = 'LOOKUP_FIN_COSTTYPECODE'
AND DATA.LANGUAGE_CODE = @WHERE:PARAM:LANGUAGE@
AND DATA.LANGUAGE_CODE = LANG.LANGUAGE_CODE
AND DATA.IS_ACTIVE = 1
AND (
(PAC.COST_TYPE like 'OPERATING%' AND DATA.LOOKUP_CODE like 'OPERATING')
OR
(PAC.COST_TYPE like 'CAPITAL%')
)
AND PAC.project_code = @WHERE:PARAM:USER_DEF:STRING:project_code@

 

I tried also options like @where:param:xml:integer:/data/id/@value@ (with = PAC.ID) or @where:param:xml:integer:/data/ProjectID/@value@ or @WHERE:SECURITY:PROJECT:INV.ID@. Nothing worked for me. 

 

Do you know how can I use the last clause to work on current project? Thanks a lot. 

 

Matej

Outcomes