Clarity

Expand all | Collapse all

Parametrized lookup based on Project ID

  • 1.  Parametrized lookup based on Project ID

    Posted 02-13-2018 07:55 AM

    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



  • 2.  Re: Parametrized lookup based on Project ID

    Posted 02-13-2018 10:06 AM

    Somewhat similar to this?

     

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

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

     

    NJ



  • 3.  Re: Parametrized lookup based on Project ID

    Posted 02-14-2018 02:34 AM

    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