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....
FROM CMN_LOOKUPS_V DATA, CMN_LANGUAGES LANG, PAC_MNT_PROJECTS PAC
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
(PAC.COST_TYPE like 'OPERATING%' AND DATA.LOOKUP_CODE like 'OPERATING')
(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.