AnsweredAssumed Answered

Dynamic Multi Valued Lookups

Question asked by Maddineni on Oct 31, 2010
Latest reply on Nov 2, 2010 by Maddineni

We have requirement to display 2-multivalue attribute lookup values on a BO XI crystal report.These 2-lookup attributes has created on Project object.Among 2-Multi Valued Lookups 1-is dynamic type MVL and 2nd one is static type MVL.

To populate Static MVL attribute values we have created one Static MVL.For Dynamic MVL attribute we are using the outofthebox query (Active and Locked Resources) to populate the values.

we have verified the values for both the lookups and both the lookup values are getting stored on ODF_MULTIVALUED_LOOKUPS TABLE.value

we able to display the values if we write 2-diffrent queries.Such as

Dynamic MVL Lookup values:

select oml.value,sr.full_name
from clarity.srm_resources sr,clarity.odf_multi_valued_lookups oml
where to_char(oml.value) = to_char(sr.user_id)
and oml.pk_id = '5000342'

Static MVL Lookup values:

select om2.pk_id , , cv.lookup_type, from clarity.cmn_lookups_v cv ,clarity.odf_multi_valued_lookups om2
where om2.value = cv.lookup_code
and cv.lookup_type = 'abc_xyz'and cv.language_code = 'en'
and om2.pk_id = '5000342'

For Static MVL values : ODF_MULTIVALUED_LOOKUPS.value = cmn_lookups_v.code

For Dynamic MVL : ODF_MULTIVALUED_LOOKUPS.value = srm_ressources.user_id

Now my confusion is how do we join ODF_MULTIVALUED_LOOKUPS table with cmn_lookups_v & srm_resources in 1-query itself.

Please advise us if i missed any other tables.

Best Regards