Clarity

Hi All,

We have a OBS and we have attached one instance to the OBS unit.

We gave "Instance" Access Right to that particular Attached Instance.

for example: I have selected 'PROJECT' object and assigned 'project-edit' right to a project,after that I have selected 'IDEA' object and assigned 'Idea-edit' right to an Idea.

Now i would like to get the associated instances i.e.,associated project name and associated Idea name.

can any one tell me how to do that?

Thanks in Advance!!

Hi,

You can view the associated instances from GUI as well.. just go to OBS Unit -> Attached instances under properties -> Object as Project or Idea-> expand filter -> show all

or

Select * from  prj_obs_associations  where unit_id = id of OBS Unit.

-Gurjeet

I don't want this from GUI.I want it in the query.

In the query I want to display that Attached Instance and associated instances for those attached instances.

Please provide another example what you are looking for??

Below is the query which will tell you all the associated instances to OBS Units.

Select * from  prj_obs_associations  where unit_id = id of OBS Unit.

record_id in the above query is Object id (project id or idea id).

In case you are looking to join with access rights then cmn_sec_assgnd_right (e.g instance id in table is  project id)

-Gurjeet

Hi,

You can try below query. We have used it many times for extracting instances of instance:

edit the bold portions to insert username and right id:

-----------------------------------------------------------------------

select ins_rght.odf_pk,
ins_rght.unique_code ins_project_id,
ins_rght.name ins_project_name,
ins_rght.manager_id_caption project_manager,
(select srm.unique_name from srm_resources srm,cmn_sec_users cmn where srm.user_id=cmn.id and cmn.id=ins_rght.manager_id) manager_unique_name,
ins_rght.principal_id,(select last_name||', '||first_name from cmn_sec_users cmn where cmn.id=ins_rght.principal_id) resource_name,
(select srm.unique_name from srm_resources srm,cmn_sec_users cmn where srm.user_id=cmn.id and cmn.id=ins_rght.principal_id) resource_unique_name,
ins_rght.right_id,
decode(ins_rght.right_id,'3601','Project - Edit','3607','Project - Edit Project Plan','3606','Project - Modify Baseline','5073143','Project - Proected Subpage Attribute') right_name
from

(select odf_q.*  , (select name from ( select o.code code,
               nls.name name,
               nls.description description
        from   odf_objects o, cmn_captions_nls nls
        where  o.id = nls.pk_id
        and    nls.language_code = 'en'
        and    nls.table_name = 'ODF_OBJECTS'
        and    1=1 and 1=1) q_odf_object_code where rownum = 1 and q_odf_object_code.code = odf_q.odf_object_code) odf_object_code_caption  , (select name from ( SELECT C.CODE CODE,
        nls.name NAME
        FROM ODF_CLASS C,cmn_captions_nls nls, odf_objects oo
        WHERE nls.table_name = 'ODF_CLASS'
        AND oo.IS_CLASS_ENABLED = 1
        AND oo.code = c.object_code
        AND nls.pk_id = c.id
        and 1=1 and 1=1 and nls.language_code = 'en' AND ('12' IS NULL OR
           '12' = C.OBJECT_CODE)
         AND ('12' IS NULL OR
              '12' != c.CODE)) q_odf_class_code where rownum = 1 and q_odf_class_code.code = odf_q.odf_class_code) odf_class_code_caption  ,
              (select b.name from cmn_partitions a,cmn_captions_nls b  where a.id = b.pk_id and b.table_name = 'CMN_PARTITIONS' and  b.language_code = 'en' and a.code = odf_q.partition_code union  select name from cmn_captions_nls  where pk_id = -1 and table_name = 'CMN_PARTITIONS' and language_code = 'en' and 'NIKU.ROOT' = odf_q.partition_code) partition_code_caption  ,
              (select full_name from ( SELECT  u.id user_id,
                    u.user_name user_name,
                    u.user_name UNIQUE_CODE,
                    r.id resource_id,
                    r.unique_name unique_name,
                    r.first_name first_name,
                    r.last_name last_name,
                    r.full_name full_name,
                    r.unique_name resourceID,
                    r.person_type person_type_id,
                    l.name person_type,
                    u.user_status_id user_status_id,
                    s.name user_status
                FROM    srm_resources r,
                            cmn_sec_users u,
                            cmn_lookups_v l,
                            cmn_lookups_v s
                WHERE   u.id = r.user_id
                    AND     1=1 and 1=1 AND     r.person_type = l.id
                    AND     l.language_code='en'
                    AND     l.lookup_type='SRM_RESOURCE_TYPE'
                    AND     u.user_status_id = s.id
                    AND     s.language_code= 'en'
                    AND     s.lookup_type='SEC_USER_STATUS'
              
                    AND 1=1) q_manager_id where rownum = 1 and
                    q_manager_id.user_id = odf_q.manager_id) manager_id_caption 
                    from ( select row_number() over ( order by name asc, odf_pk)  odf_row_num, count(*) over (partition by pmd_analytical_partition_by)  odf_num_rows ,
                    odf_cols.* from (  select distinct   'x' pmd_analytical_partition_by, inv_investments.id odf_pk  ,
                     inv_investments.schedule_start schedule_start  , inv_projects.percent_complete percent_complete  ,
                     inv_investments.IS_ACTIVE is_active  , odf_ca_inv.odf_object_code  odf_object_code  ,
                     inv_investments.schedule_finish schedule_finish  , inv_investments.name  name  ,
                      odf_ca_project.odf_class_code  odf_class_code  , inv_projects.IS_PROGRAM is_program  ,
                      cmn_sec_assgnd_obj_perm.principal_id principal_id,cmn_sec_assgnd_obj_perm.right_id right_id,
                       odf_ca_project.partition_code  partition_code  , inv_investments.MANAGER_ID manager_id  ,
                       inv_investments.code  unique_code  , (NVL(INV_INVESTMENTS.labor_actsum,0)/3600)/(select hours_per_day from prj_hours_per_day_v) actuals  
                       from inv_investments inv_investments LEFT OUTER JOIN prj_ev_history EVREC ON EVREC.OBJECT_ID = INV_INVESTMENTS.ID
                       AND EVREC.OBJECT_TYPE='PROJECT' AND EVREC.PERIOD_NUMBER=0
                       LEFT OUTER JOIN prj_baseline_details BASEREC ON BASEREC.BASELINE_ID = INV_INVESTMENTS.BASELINE_ID
                       AND BASEREC.OBJECT_TYPE='PROJECT', inv_projects inv_projects, srm_resources CREATED,
                       srm_resources UPDATED, odf_ca_inv odf_ca_inv, fin_financials fin_financials,
                       odf_object_instance_mapping oim7, odf_ca_financials odf_ca_financials,
                       pac_mnt_projects pac_mnt_projects, odf_ca_projfinproperties odf_ca_projfinproperties,
                       odf_ca_project odf_ca_project  , cmn_sec_assgnd_obj_perm where 1 = 1 and cmn_sec_assgnd_obj_perm.principal_type = 'USER'             
                       and INV_INVESTMENTS.ID = cmn_sec_assgnd_obj_perm.object_instance_id             
                       and cmn_sec_assgnd_obj_perm.principal_id = 5026180                        --- resource id from cmn_sec_users        
                       and cmn_sec_assgnd_obj_perm.right_id = '3601'                             --- instance right id 
                       and inv_investments.id = inv_projects.prid and inv_investments.created_by = CREATED.user_id
                       and inv_investments.last_updated_by = UPDATED.user_id and inv_investments.id = odf_ca_inv.id
                       and inv_investments.id = oim7.primary_object_instance_id  and oim7.primary_object_instance_code = 'project'
                       and fin_financials.id = oim7.secondary_object_instance_id  and oim7.secondary_object_instance_code = 'financials' 
                       and FIN_FINANCIALS.id = odf_ca_financials.id and inv_investments.id = pac_mnt_projects.id
                       and inv_investments.id = odf_ca_projfinproperties.id and inv_investments.id = odf_ca_project.id
                       and odf_ca_inv.odf_object_code = 'project' and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and
                       1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1
                       and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1 and 1=1    ) odf_cols ) odf_q 
                       where odf_q.odf_row_num between 1 and 20 )ins_rght
                       order by 5,10

Thanks,

Pragya Singh

Personally i believe pasting big queries is not good practice, specially when you have hugely customized owned systems. We need to direct user to right direction rather than giving ready made solutions or confused Solution with customizations.

Users can learn more with right directions rather than solutions.

(my views)

-Gurjeet

Agreed