Clarity PPM

Expand all | Collapse all

How to relate OBS with Project object?

  • 1.  How to relate OBS with Project object?

    Posted 10-03-2012 07:55 AM
    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.


  • 2.  RE: How to relate OBS with Project object?

    Posted 10-03-2012 08:05 AM
    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.


  • 3.  RE: How to relate OBS with Project object?

    Posted 10-03-2012 08:48 AM
    Hi Martti,

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

    Thanks for prompt reply.


  • 4.  RE: How to relate OBS with Project object?

    Posted 10-03-2012 09:54 AM
    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.


  • 5.  RE: How to relate OBS with Project object?

    Posted 10-03-2012 09:54 AM
    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


  • 6.  RE: How to relate OBS with Project object?

    Posted 10-03-2012 09:55 AM
    Snap!


  • 7.  RE: How to relate OBS with Project object?

    Posted 10-03-2012 11:06 AM
    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.


  • 8.  RE: How to relate OBS with Project object?

    Posted 10-09-2012 01:43 PM
    Hi,

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

    Thanks!
    Chris