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.
Somewhat similar to this?
Parameterized Lookup - Project internal ID isn't passed into the parameter.
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...