Clarity

Thanks for sharing this Dave!

dtietze wrote:

The way I understand this, is that all your logic is in the display part of the lookup -- meaning that the database column for the attribute in the background will always hold a "1".

dtietze wrote:

This might be OK for displaying stuff in Clarity

dtietze wrote:

, but it means the actual value is not available for reporting, etc. Also, as you note, you'll run into some hassle in list portlets.

dtietze wrote:

Why not just make an attribute of the desired type, make that read-only, use a process and an sql:update to correctly set the value for the attribute?

A build on this technique from Gene with regard to displaying icons instead of just text in a dynamically built hyperlink;

gcubed wrote:

 

Dave,

 

I was thinking about the icon url.

 

If you loaded a set of icons in the knowledge store:

 

You would be able to access them via there URL link.  For example the devil_smile.gif is located at this URL:

 

https://cppm.ondemand.ca.com/niku/app?action=dms.viewFile&RhXm0r7tSeUqEr=true&fileId=5272209&fileName=devil_smile.gif

 

So in your “How To Put Any Dynamically Generated Value On A Clarity Object” taking the following NSQL for dynamic links:

 

SELECT

@SELECT:1:DUMMY@

,@SELECT:X.details:template_details@

FROM

(

SELECT

'<a href="'||T.template_link||'" target="_blank">Link to TEMPLATE</a>'

END

AS details

FROM

odf_ca_****** T

JOIN odf_ca_YYYYYY R ON R.item = T.ID

WHERE R.ID = @WHERE:PARAM:USER_DEF:INTEGER:object_id@

UNION

SELECT 'DUMMY' FROM DUAL

WHERE @WHERE:PARAM:USER_DEF:INTEGER:object_id@ IS NULL

) X

WHERE @FILTER@

 

By modifying the following line from:

'<a href="'||T.template_link||'" target="_blank">Link to TEMPLATE</a>'

 

To

'<a href="'||T.template_link||'" target="_blank"><img src="https://cppm.ondemand.ca.com/niku/app?action=dms.viewFile&RhXm0r7tSeUqEr=true&fileId=5272209&fileName=devil_smile.gif" />

</a>'

 

I would expect the devil_smile.gif icon to appear rather than the “Link to TEMPLATE” text link.

 

V/r,

Gene

Cool!

Hi! I've tried this solution and for some reason i got some anomalies :(. Meaning that the relevant field in database contains NULL (not 1) after saving page in clarity (where I use this dynamic lookup). And therefore - after saving > the lookup does not show any values.... And I could not figure out, why this is happening

Lookup SQL is as following:

SELECT
  @SELECT:1:DUMMY@,
  @SELECT:Q.JIRANOTICE:JIRANOTICE@
FROM (
  SELECT CASE WHEN COUNT(i.ID) > 1 THEN 'Existing' ELSE 'Not Existing' END AS JIRANOTICE
  FROM INV_INVESTMENTS i INNER JOIN ODF_CA_INV ci ON i.ID = ci.ID
  WHERE ci.jira_id = @WHERE:PARAM:USER_DEF:STRING:JIRA_ID@
  UNION
  SELECT 'Not Existing' JIRANOTICE
  FROM DUAL
  WHERE @WHERE:PARAM:USER_DEF:STRING:JIRA_ID@ IS NULL
) Q
WHERE @FILTER@

The value on the database always has to be '1' ; so check your settings for "default value" on the attribute and make sure that all values on the database are '1' - then check what happens when you re-save that page, it sounds like perhaps the lookup is not returning a '1' for you but I'm not sure how that would happen.

Hi! That's what's suprising me also, that i have default value = 1 set in investment level (because the attribute is in investment level)...and also idea level...when i'll do in investment level - Populate Null Values with the Default - i see in the DB, that the value is 1 in database. But when saving data in relevant idea, it saves NULL to DB. Don't understand why...

^  bit confused; IDEA and INVESTMENT are separate objects - you have the dummy field on on the INVESTMENT so what do you mean by "saving data in relevant idea"? Are you talking about converting an IDEA to an INVESTMENT (in which case the problem might be what the dummy value gets set to upon the conversion process)???

Hi Dave,

I have tried the above trick to display the Parent value of the Investment Type lookup. I am using the below SQL to display the value.

The issue is that it is not populating the value for some of the project. Any idea ?

SELECT

@SELECT:1:DUMMY_ID@

,@SELECT:RESULT.PLKUP_VAL:PLKUP_VALUE@

FROM

(

SELECT PARENT_LKUP.NAME PLKUP_VAL

FROM CMN_LOOKUPS_V CHILD_LKUP

INNER JOIN CMN_LOOKUPS_V PARENT_LKUP ON CHILD_LKUP.PARENT_LOOKUP_CODE = PARENT_LKUP.LOOKUP_CODE

INNER JOIN INV_INVESTMENTS INV ON CHILD_LKUP.LOOKUP_CODE = INV.STAGE_CODE

WHERE INV.ID = @WHERE:PARAM:USER_DEF:INTEGER:inv_id@

AND PARENT_LKUP.LOOKUP_TYPE = 'INV_PROCESS_TYPE'

AND CHILD_LKUP.LANGUAGE_CODE = 'en'

AND PARENT_LKUP.LANGUAGE_CODE = 'en'

AND CHILD_LKUP.IS_ACTIVE = 1

AND PARENT_LKUP.IS_ACTIVE = 1

UNION

SELECT to_char(null) from DUAL

WHERE @WHERE:PARAM:USER_DEF:INTEGER:inv_id@ is NULL

)RESULT

WHERE @FILTER@

Thanks

HT

The SQL looks fine to me.

What do you mean by "not populating the value for some of the project" - what are the differences between the project that it does populate for and those that it doesn't?

The difference between the 2 projects is that one is having inactive lookup value, so what i did that re-activate the lookup value, check the checkbox "populate null values with the default" in the attributes and then again de-activate the lookup value.

Now it is showing the parent value if we select the child.

Thanks

HT

Ok - I'll assume you are OK now!

The thing about the "dummy lookup" in this technique though is that is always returns the same value (1), it is just the "display value" of the lookup which is "dynamic" and derived from the object-context.

It is me again and I've copied this from the shortening long URL thread

Now I've manage to make a couple of implementations of those rather smoothly and are banging the limitations hard.

Is it correct that color mapping is not possible if the data is numeric, because there is  lookup and it is dynamic and not static?

Secondly the values displayed in the properties view are not stored in the database (like in the CMN_CAPTIONS_NLS) but always calculated on the fly?

So I I want the values in a portlet I would need the include the same query as in the lookup to the query of the portlet?

Is it correct that color mapping is not possible if the data is numeric, because there is  lookup and it is dynamic and not static?

 

yeah - the "actual value" is always going to be "1" so the "normal Clarity-way" that you would map to a colour (or an icon) will not help us ; BUT Gene posted a build on "my" technique (which I reposted earlier in this thread) in order to display icons - essentially doing the mapping as part of the (dynamically constructed) html in the lookup ; so you should be able to do what it sounds like you are thinking about using that.

Secondly the values displayed in the properties view are not stored in the database (like in the CMN_CAPTIONS_NLS) but always calculated on the fly?
So I I want the values in a portlet I would need the include the same query as in the lookup to the query of the portlet?

Yes, just include the logic in the portlet's query - earlier in this thread I said "For "reporting" you would just have to derive the "calculated value" in the reporting solution (NSQL portlet/report/etc) [ if you encapsulate the calculation logic in a database package then you could use the exact same "code" for reporting as in the hack/lookup ]" that is was I was referring to there.

Thank you for your patience and sharing your wisdom.

Now the calculated attribute and the portlet are a little more than a week old and suddenly no more calculation are made. All the values are blank. The query based portlet still displays values as before.

If the calculated attribute worked properly for a while and and nobody fiddled it should be OK and if not I can create another just to be sure, would that be a system issue underlying?

Because nothing happens there are of course no errors.

Well clearly "something" has gone wrong - this trick doesn't expire over time (I have many attributes many years old using it! )

What I would look at it this happened to me would firstly be the underlying data ; is the attribute value still "1" on *ALL* records on the underlying table? (if not ; check the attribute "default value" in the object/attribute set up - remember that that lookup always needs to return "1", even when on that object/attribute page)

If thats all OK, I'd double check the NSQL logic in the lookup.

After that I'd be a bit at a loss....

Just to be sure I created it again with a new lookup and it worked fine.

So I replaced the original and relabeled the new with the old name and the users don't see the difference only that now it is working.

Thanks for your response.

Hi Dave,

great tip, but I have a question. When tried to implement this on CA Sandbox v. 14.2, it worked well according to your guide.

However when tried to implement on our customer Clarity v.13.3 it not shown me "Object_ID" in Lookup Param Mapping in my Dummy attribute.

Would you know what could cause it? Might it be the version? Query in Lookup is of course the same. I've just used 'project' object, but it also not worked with 'risk' object. Thanks

In Clarity 13.3:

In Clarity Sandbox 14.2:

Matej

^ Ok that looks very weird - but nothing to do with "my trick" I think - just a general "parametrised lookup" issue.

I've never seen that sort of thing, maybe a but in 13.3 with parametrised lookups, but not one I'm aware of, sorry.

Hmm, OK. Dave, when you don't know to help, then nobody so we have to accept this situation. Thanks again for checking.

Note: Just one more question I've just had in my mind: Does the CA Sandbox exist also for ver. 13.3 yet, or not? To be able to try on fresh installation...

Matej

I've never used the sandbox so I wouldn't know!

But CA support might know if there are any known issues with parameterised-lookups in 13.3?

OK I'll ask for this, thanks.

Matej

Hi,

I have the same issue. I am trying to build a Dynamic lookup for Booking manager  in the Team object. I could not get the PRID in the Object list and so I took the PRUID as the parameter to pass. I am getting the value on the Resource workload Portlet if I hard code the PRUID. But then I pass the variable and in the Team Lookup when I connect the variable to the PRUID, I do not get any value on the resource workload portet. Any help is appreciated. My code looks like this:

SELECT

@SELECT:1:DUMMY_ID@

,@SELECT:X.BOOK_MNG:BOOK_MNG@

FROM

(

SELECT (SELECT SRM.FULL_NAME FROM SRM_RESOURCES SRM WHERE SRM.USER_ID = REQ.BOOK_MANAGER_ID) AS BOOK_MNG

  FROM PRTEAM TEM

  LEFT OUTER JOIN RSM_REQ_REQUISITIONS REQ

  ON REQUIREMENT_ID=TEM.PRID

  AND REQ.OBJECT_ID=TEM.PRPROJECTID

  AND REQ.OBJECT_TYPE = 'SRM_PROJECTS'

  AND REQ.STATUS_CODE NOT IN ('BOOKED','CLOSED')

WHERE 'prteam'+CAST(REQ.REQUIREMENT_ID AS VARCHAR(7)) = @WHERE:PARAM:USER_DEF:STRING:pruid@

UNION

SELECT ISNULL(NULL,'NA') from DUAL

WHERE @WHERE:PARAM:USER_DEF:STRING:pruid@ is NULL

) X

WHERE @FILTER@

You should be able to use PRTEAM.PRID in your query and link that to the "[--Object ID--]" in the lookup mapping I would have thought?

[ Don't think I've ever seen any "good code" ever trying to join data on those PRUID columns, they have always seemed to me to be some sort of throwback to old-technology ]

Thanks for the tip. I tried this. But I still do not get any results. Again, if I hard code the PRID, I do get the results

My code looked as given below:

SELECT

@SELECT:1:DUMMY_ID@

,@SELECT:X.BOOK_MNG:BOOK_MNG@

FROM

(

SELECT (SELECT SRM.FULL_NAME FROM SRM_RESOURCES SRM WHERE SRM.USER_ID = REQ.BOOK_MANAGER_ID) AS BOOK_MNG

  FROM RSM_REQ_REQUISITIONS REQ

  INNER JOIN PRTEAM TEM

  ON REQ.REQUIREMENT_ID=TEM.PRID

WHERE REQ.OBJECT_TYPE = 'SRM_PROJECTS'

  AND REQ.OBJECT_TYPE = 'SRM_PROJECTS'

  AND  TEM.PRID=@WHERE:PARAM:USER_DEF:INTEGER:object_id@

  UNION

SELECT ISNULL(NULL,'NA') from DUAL

WHERE @WHERE:PARAM:USER_DEF:INTEGER:object_id@ is NULL

) X

WHERE @FILTER@

Mohan.K I can only suggest that you "double-check" all the different things that need to be done to get this technique to work ; your NSQL does not look wrong but there are a number of important other steps that need to be done correctly to get this to work. Refer to the advice I have given to one of the (many) other people who make some random "doesn't work" comment without fully checking all the steps! (In your case I would perhaps first check that the database value is "1" for all records, missing that step would certainly give the symptoms that you describe (but the "problem" could also be elsewhere)).

Worse case : go back to my original document, do everything in that document exactly as I have done - see the technique working, only then modify it for your local requirement.

Actually we are on 14.3 and are seeing the same thing above. It works, but on new records that are create din CA PPM it does not work. To resolve the situation we have to go back into the attribute and select "Populate Null Values with the Default" and press the "Save" button again. Then the new records work. Unfortunately, we cannot do this every-time a new record is created. 

Interesting behavior.

^ sounds like a problem with the defaulting of the attribute then (rather than the "trick" specifically) ; do other 'normal' attributes (with a default) get populated OK on record creation?

Other normal attributes are fine with the defaults on creation. Just this one. I created a test in another environment and the results were the same. I may give it a go in 14.4 sandbox tonight.

Thanks!

Thats really weird, as far as the object is concerned its 'just' an attribute with a default value - might have to get CA Support onto the case

Since a lot of my work was moved to a new org, i have to have them open the ticket with CA support.

Thanks for sharing Dave! will try it soon!

Hi Dave,

Can you please help/guide me to add dynamic url in Project edit object.On click of this dynamic url, it should open Project Alignment Rating page.

This might be similar to Originating Idea field.

Thanks in Advanced.

shital wrote:

 

Hi Dave,

 

Can you please help/guide me to add dynamic url in Project edit object.On click of this dynamic url, it should open Project Alignment Rating page.

This might be similar to Originating Idea field.

 

Thanks in Advanced.

Well, the lookup that you need to write just needs to provide the URL that you want to link to as its "display value" - follow the (fairly explicit) instructions in that document I posted originally and you should get it all working.

There is a little discussion on getting URL's with this technique in this thread

https://communities.ca.com/thread/54954980

I try to access the post (TIP : How To Put Any Dynamically Generated Value On A Clarity Object ), and I get the below message:

NJ

Although, I can access the post from the inbox

NJ

[OFFTOPIC] so you post in thread https://communities.ca.com/thread/99705433 that you can't access thread https://communities.ca.com/thread/99705433 and then you post again saying that you can?

Was any of that really worthwhile, or appropriate to this thread? (and I include my post now in that comment)

Hi Dave,

I referred your doc and created dummy lookup but noting is appearing in front of field (no value to click).

My requirement is - I need to create new field 'Update Project Alignment Rating' on project with type virtual string and fields value should be URL of project's sub-page.

Do you know how this originating idea implemented. I need the same for my case.

It would be great if you could provide me some details on originating idea, i will refer that.

Thanks for your help and time.

I know that this technique is able to deliver you the functionality that you are looking for ; similar to "Originating Idea"

The way that "Originating Idea" works is built into the Clarity code though, so I have no idea how that works, and even if I did there is nothing that you could do to copy it / reuse it.

--

If you follow the example in my document exactly, then you should get the same results as I do (and the many other people who have followed my document) - that would give you a working example of how this technique works. You would then be able to build your own specific functionality using that technique.

If you are stuggling with what you are doing, then you need to provide some more information than just saying it isn't working!

( As a minimum the NSQL code in your lookup, probably also screen shots of your attribute setup and the view/fields setup)

This isn't simple, but neither is it particularly hard if you do everything right - and I can't guess what you have done wrong!

Second that.

I had a problem because I had not exactly followed the instruction.

With some detail it was possible to see where the problem was in my case.

Hi Dave,

On project screen it is appearing like below, without hyperlink to ‘link to Project Alignment page’

Update Project Alignment Rating : <a href="http://nappmq01.unix.medcity.net:8080/niku/nu#action:projmgr.projectProperties&odf_view=projectCreate.subObjList.prj_stratification&id=6629037" target="_blank">Link to Project Alignment Rating PAGE</a>

But when i copied this url it took me to correct sub page 'Project Alignment page' of project.

I have attached details doc mentioned lookup details I created.

In the VIEW / FIELDS properties for your "Update Project Alignment Rating" field, make sure the lookup is set to PULL-DOWN (not BROWSE).

Thank you sooooo much Dave .

Its working in QA.

Thanks again for your help and time .

Hi Dave,

Today I deployed dynamic url to production but it is not working in production server.

I did the same changes as I made in QA. Even QA object and fields name are same in production.

But hyperlink is not showing in front of field.

Production:

dummy lookup generating below url in production:

<a href="https://claritymsp.corp.medcity.net:8048/niku/nu#action:projmgr.projectProperties&odf_view=projectCreate.subObjList.prj_stratification&id="

target="_blank">Link to Project Alignment Rating Page</a>

I am using below url to login to clarity.

https://nappmp04.unix.medcity.net:8048/niku

I am not sure what is going wrong. Please please help me here.

Well its hard to debug your code from here ; but what it looks like is that the dummy-attribute on the object is not set to "1" (so the lookup is not "looking anything up") - check the database to see if all records are set to "1" and make sure the dummy attribute is set right (for old records) as well as it being the default for any newly created records.

EDIT: or possibly the parameter just isn't being passed into the lookup - check the mapping on the dummy-attribute properties on the object, make sure the parameter mapping is set!

I checked everything and did in same way as QA. I have no idea how it is coming up on QA and not in production.

I have attached document with screenshots which shows the dynamic query/lookup and mapping of attribute with dummy value. Please check.

I am not sure why production url is showing

https://claritymsp.corp.medcity.net:8048/  

instead of  https://nappmp04.unix.medcity.net:8048/

Please help me here.Yesterday till 1 am I done the changes in production and today this part is not coming up .

When I said check the database to see if all records are set to "1" - have you done this?

ie SELECT DISTINCT update_pro_all_rat FROM odf_ca_project

If you missed the "populate null values with the default" setting in the object attribute then 'old' values will still be NULL - *ALL* values must be the default.

--

Your URL content is picked up from the NSA settings saved into the database ; check your scheduler setting in the NSA and make sure that is saved to the database (check the CMN_CONFIG table). Or you could just "hard code" the URL in the lookup of course (this is nothing to do with the "technique")

Hi Dave,

Thanks for all your help Dave .

I can see the hyperlink now, but in production when I click on the link I either get a ‘page cannot be displayed’ or prompt to log into Clarity. It does not take me directly to the  subpage (PAR page) as in QA.

Do you have any idea?

^ well that sounds like there is something "wrong" with your URL (so nothing to do with this "technique" as such, but you need to check the URL that you are generating in the lookup)

I used the same QA dynamic query as QA and production fields /object name are same.

URL is coming correct but even if I logged in clarity ,hyper link prompt me to login again.

It is this due to https?security?

Query:

SELECT 

@SELECT:1:DUMMY@

,@SELECT:X.link_details:link_details@

FROM

(

SELECT '<a href="'||to_char(substr(VALUE,instr(VALUE ,'schedulerUrl="')+14,instr(substr(VALUE,instr(VALUE ,'schedulerUrl="')+14),'"')-1))||'/niku/nu#action:projmgr.projectProperties&odf_view=projectCreate.subObjList.prj_stratification&id='||@WHERE:PARAM:USER_DEF:INTEGER:object_id@||'" target="_blank">Link to Project Alignment Rating PAGE</a>' link_details

FROM cmn_config

WHERE NAME = 'properties.xml'

UNION

SELECT 'DUMMY' FROM DUAL

WHERE @WHERE:PARAM:USER_DEF:INTEGER:object_id@ IS NULL

) X

WHERE @FILTER@

Well is the URL produced correct? Is it pointing to the same Clarity application/server that you are logged into?

DAve,

No ,and that is what i am wondering the url is different than i logged in.

URL coming up on hyperlink :https://claritymsp.corp.medcity.net

URL I logged in :  https://nappmp04.unix.medcity.net:

any thoughts?

This is what I was saying earlier;

Your URL content is picked up from the NSA settings saved into the database ; check your scheduler setting in the NSA and make sure that is saved to the database (check the CMN_CONFIG table). Or you could just "hard code" the URL in the lookup of course (this is nothing to do with the "technique")

So you could just simplify your lookup logic - i.e. HARD CODE THE RIGHT URL in the lookup rather than trying to derive it from the database (how you generate the URL is irrelevant to the technique, that is up to YOU to solve!)

You can additionally refresh the page using this tip

CA PPM Tuesday Tip - Auto refresh an object instance once process runs

More help for dummies needed please.

I have trying to get this work for a particular purpose.

I have got the query and both sides of the union seem to work in MS SQL enterprise manager.

When I preview the lookup I get one record with value 1 and blank in the next column (so I can't say if it returns null, but that part returns null in MS SQL tool)

In the attribute properties everyhing goes as instructed until I hit Save. Then I get the message that a read only attribute must have the default specified. I did select the record for default without problem, but apparently that is not considered to have a value. What might be the way to deal with that? My query is below

SELECT 

@SELECT:1:DUMMY@

,@SELECT:X.SUCCESSOR_IDS:SUCCESSOR_IDS@

FROM

(( SELECT Distinct  PPRED.PRPREDTASKID TASK_ID,     

( SELECT   cast(PRSUCCTASKID as varchar)  + ', ' AS 'data()'

FROM PRDEPENDENCY SUCC

WHERE SUCC.PRPREDTASKID = PPRED.PRPREDTASKID

FOR XML PATH('') ) SUCCESSOR_IDS FROM PRDEPENDENCY PPRED WHERE cast(PPRED.PRPREDTASKID as  varchar)   = @WHERE:PARAM:USER_DEF:STRING:task_prid@)

UNION

SELECT 1 field2, cast(null as varchar) from DUAL

WHERE @WHERE:PARAM:USER_DEF:STRING:task_prid@ is NULL

) X

WHERE @FILTER@

Might just be that "null" as your dummy return value confusing the attribute page (I assume that that column would be the 'display value' so the attribute page would actually be displaying 'null') - does it work OK if you replace your cast(null as varchar) with 'dummy value' for example?

I am attempting to use this concept on V13.3 Assignment object to calculate ETC Cost using the ETC Hours * Rate Matrix Value.

I've extremely simplified the Look-up query to be:

SELECT 
 @SELECT:1:DUMMY@
,@SELECT:X.etc_cost:etc_cost@
FROM
(
select
10 etc_cost
from dual
  where @WHERE:PARAM:USER_DEF:STRING:object_id@ IS NOT NULL
UNION
SELECT 20 FROM DUAL
WHERE @WHERE:PARAM:USER_DEF:STRING:object_id@ IS NULL
) X

WHERE @FILTER@

Created a new attribute on the Assignment, and can see from the database it is populated as the value 1

On the new attribute I've linked the parameter OBJECT_ID (as defined in the above look-up query) to be pruid.

However, when I display the new attribute on the Assignment list view, the value 20 is always shown - this should not be the case, it should show 10 as the PRUID should not be NULL.  I've also attempted to use INTEGER instead of STRING in the above, but again with no luck. 

Any suggestions, as love this approach and have used this in other environments (though they have been later versions of Clarity) and never previously on the Assignment object.

This technique does not work on the LIST views, only on the EDIT page - this limitation has been highlighted several times!

Hi Dave,

This is a great tip and I have been using this for a few of my requirements and works like charm.

But recently I used it up for displaying a concatenated value (Benefit plan Code - Benefit Plan Name).

I have followed the steps as it is on your document. but sadly my dummy attribute seems to be holding 'Null' instead of the value '1'. Every time I make a new instance using this attribute I am having to do a populate null with defaults which is not feasible. The lookup though is returning the correct value as I can see the correct value when I keep the field open for editing (i.e. not making it read only).

Reading through this thread I see i mightbe facing an issue already existing, something like the attribute not picking up the default value.

Any resolution ideas on this would be greatly helpful.

Meenakshi

If the attribute is set to NULL when you create a new instance of the object then this sounds like the attribute default is not set correctly - you obviously think that you have set that correctly though so I would suspect that the NSQL lookup you are using does not deliver a "1" against the dummy value where there is no parameter value passed into the lookup - that would explain your issue certainly.  

Post the NSQL if that doesn't make sense?

Hi Dave,

Here is my NSQL

SELECT
@SELECT:1:DUMMY_ID@
,@SELECT:X.BENEFIT_PLAN_CODE_NAME:BENEFIT_PLAN_CODE_NAME@
FROM
(
SELECT DISTINCT
FPB.CODE ||'-'|| FPB.NAME BENEFIT_PLAN_CODE_NAME
FROM RIM_RISKS_AND_ISSUES CR
INNER JOIN INV_INVESTMENTS INV ON INV.ID=CR.PK_ID
INNER JOIN ODF_CA_CHANGE ODF ON ODF.ID=CR.ID
INNER JOIN FIN_PLANS FP ON FP.ID = ODF.SUB_BUD_REVISION
INNER JOIN FIN_PLANS FPB ON FPB.ID = FP.BENEFIT_PLAN_ID
WHERE CR.ID=@WHERE:PARAM:USER_DEF:INTEGER:ri_id@
UNION
SELECT NULL from DUAL
WHERE @WHERE:PARAM:USER_DEF:INTEGER:ri_id@ is NULL)X

Lookup windows

Meenakshi v

Ok doesn't look wrong ; only thing I would GUESS at is that the dummy NULL value might be an issue.

ie.

UNION
SELECT NULL from DUAL
WHERE @WHERE:PARAM:USER_DEF:INTEGER:ri_id@ is NULL

can you try something else, e.g.

UNION
SELECT 'Nothing' from DUAL
WHERE @WHERE:PARAM:USER_DEF:INTEGER:ri_id@ is NULL