Clarity

Hi All,

I want to find Service Line OBS value in Project. I am unable to find related field from back end. can any one help to find OBS value in project?

I have attached few snap shots:

1. Service line obs in project.png - It shows OBS field in project.
2. OBS unit values.png - It shows values found in OBS menu for Service line OBS.
3. OBS From menu.png - OBS Values from administrator menu.

I am not sure how i can find obs value assigned to a project? which table and field denotes this information? Please help me asap.

Thanks in advance.

You can start with something like

select
INV_INVESTMENTS.id,
INV_INVESTMENTS.code,
INV_INVESTMENTS.name,
prj_obs_units.ID OBS_ID,
prj_obs_units.Unique_name OBS_unique,
prj_obs_units.name OBS_name,
prj_obs_types.id Type_id,
PRJ_obs_types.unique_name Type_unique,
PRJ_obs_types.name OBS_type_name
from
INV_INVESTMENTS,
niku.prj_obs_units,
niku.prj_obs_associations,
niku.prj_obs_types
where
prj_obs_associations.record_id=INV_INVESTMENTS.id
and odf_object_code= 'project'
AND prj_obs_associations.UNIT_ID=prj_obs_units.id
and prj_obs_associations.table_name= 'SRM_PROJECTS'
and prj_obs_types.id= prj_obs_units.type_id
order by PRJ_obs_types.name

for those proejcts which are associated with an OBS units


Martti K.

Hi Martti,

The query gives me OBS Name. But I want full OBS Path. How can i get it?

Thanks for prompt reply.

Have a look at the NBI_GET_OBS_* functions in the database and see if they can help provide the data in the format you're looking for, if so you can incorporate calling them in your query to save you on reinventing the logic. Just if you do, check during upgrades to see if the contents/existence of those functions change or not.

You could use the datamart function NBI_GET_OBS_flat_PATH_FCT, which returns the full path, so in Martti's query add this as a column:

niku.NBI_GET_OBS_flat_PATH_FCT(prj_obs_units.id)

Owen

Snap!

You could also try

select
inv_investments.id,
inv_investments.name,
inv_investments.code,
prj_obs_types.id Type_id,
PRJ_obs_types.unique_name Type_unique,
PRJ_obs_types.name OBS_type_name,
prj_obs_units.ID OBS_ID,
prj_obs_units.Unique_name OBS_unique,
prj_obs_units.name OBS_name,
OBS_path.OBS_full_PATH
from
inv_investments,
prj_obs_units,
prj_obs_associations,
prj_obs_types,
(SELECT

prj_obs_types.name OBS_type_nAME,
(
ISNULL(Parent10.name,'') + ISNULL(Parent9.name + '/','') + ISNULL(Parent8.name+'/' ,'') + ISNULL(Parent7.name + '/','') +
ISNULL(Parent6.name + '/','') ISNULL(Parent5.name  '/','') + ISNULL(Parent4.name + '/','') + ISNULL(Parent3.name + '/','') +
ISNULL(Parent2.name + '/','') + ISNULL(Parent1.name + '/','') + prj_obs_units.name) OBS_full_PATH, prj_obs_units.id OBS_unit_id

from

prj_obs_types,
prj_obs_units left join prj_obs_units Parent1 on prj_obs_units.parent_id=Parent1.id
left join prj_obs_units Parent2 on Parent1.parent_id=Parent2.id
left join prj_obs_units Parent3 on Parent2.parent_id=Parent3.id
left join prj_obs_units Parent4 on Parent3.parent_id=Parent4.id
left join prj_obs_units Parent5 on Parent4.parent_id=Parent5.id
left join prj_obs_units Parent6 on Parent5.parent_id=Parent6.id
left join prj_obs_units Parent7 on Parent6.parent_id=Parent7.id
left join prj_obs_units Parent8 on Parent7.parent_id=Parent8.id
left join prj_obs_units Parent9 on Parent8.parent_id=Parent9.id
left join prj_obs_units Parent10 on Parent9.parent_id=Parent10.id
where
prj_OBS_units.type_id=prj_obs_types.id
) as OBS_path
where
prj_obs_associations.record_id=inv_investments.id
AND prj_obs_associations.UNIT_ID=prj_obs_units.id
and prj_obs_associations.table_name= 'SRM_PROJECTS'
and prj_obs_types.id=prj_obs_units.type_id

and OBS_path.OBS_unit_id=prj_obs_units.id
and OBS_path.OBS_type_nAME=PRJ_obs_types.name

Order by PRJ_obs_types.unique_name, OBS_ID desc, inv_investments.name

The challenge with the functions is that they are not in tech ref and you have to go to the database to explore.

Martti K.

Hi,

Did the responses here help you resolve your issue? If so please mark the appropriate posts as Accepted Solution.

Thanks!
Chris