The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
The community will be in read-only from Monday 11:59pm (PT) to Wednesday 7:30am (PT)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

ITG_SETTINGS_ATTRIBUTES

SOLVED
Go to solution
Highlighted
Erik Cole
Honored Contributor

ITG_SETTINGS_ATTRIBUTES

Does anyone have info on how to use the ITG_SETTINGS_ATTRIBUTES tables? I'm tying to build a query that will return the values of the following Financial Management settings for all projects:

 

I think I see the values in ITG_SETTINGS_ATTRIBUTES, but it's not clear how to join back to projects.

2 REPLIES
Utkarsh_Mishra
Honored Contributor
Solution

Re: ITG_SETTINGS_ATTRIBUTES

ITG_SETTING_ATTRIBUTES and Project Table linking

SELECT sa.name attribute_name,
          sa.VALUE attribute_value,
          wp.root_task_id master_project_id,
          wp.root_task_id source_id,
          6 source_entity_id,
          sa.settings_attribute_id attribute_id
     FROM itg_settings_attributes sa,
          itg_settings_cont_attrs sca,
          itg_settings_containers sc,
          pm_projects p,
          pm_project_types pt,
          pm_work_plans wp
    WHERE     sc.settings_container_id = sca.settings_container_id
          AND sca.settings_attribute_id = sa.settings_attribute_id
          AND p.project_type_id = pt.project_type_id
          AND pt.pt_settings_key = sc.settings_container_id
          AND wp.project_id = p.project_id;

Now instead of above query can use KDRV_PROJECT_SETUP view and can use below SQL for Project Settings, example

 

select p.project_name ,p.pfm_request_id, ps.attribute_name,ps.attribute_value, KR.STATUS_CODE
from pm_projects p
join pm_work_plans wp on (wp.project_id = p.project_id AND wp.entity_type = 'WORK_PLAN')
join wp_tasks t on (t.work_plan_id = wp.work_plan_id and t.sequence_number = 0)
join kdrv_project_setup ps on ps.source_id = t.task_id and ps.source_entity_id = 6
join kcrt_requests kr on (KR.REQUEST_ID = p.pfm_request_id
                        and KR.STATUS_CODE in ('IN_PROGRESS','NEW'))
where 1=1
--and p.project_id = 47677
and ps.attribute_name like 'CE.rollupCostsToBudget'
and ps.attribute_value in ('AUTO_LABOR_MANUAL_NONLABOR','AUTO_LABOR_AUTO_NONLABOR');

 

Cheers..
Utkarsh Mishra

-- Remember to give Kudos to answers! (click the KUDOS star)
Erik Cole
Honored Contributor

Re: ITG_SETTINGS_ATTRIBUTES

Beautiful, thank you!

//Add this to "OnDomLoad" event