Clarity

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

Somewhat similar to this?

Parameterized Lookup - Project internal ID isn't passed into the parameter.

https://communities.ca.com/message/241700690 

NJ

Hi Navzjoshi, 

Yes it's the same issue exactly. However when I added those extra line with IS NULL condition:

AND
 (
 (PAC.project_code = @WHERE:PARAM:USER_DEF:STRING:project_code@)
 OR (@WHERE:PARAM:USER_DEF:STRING:project_code@ IS NULL)
 )

I got all results - so for me it seems Project_Code param is null and not passed any 'Code' as param to my lookup from Inv_Investments table...

Matej