Erik Cole Acclaimed Contributor.
Acclaimed Contributor.
533 views

ITG_SETTINGS_ATTRIBUTES

Jump to solution

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.

0 Likes
1 Solution

Accepted Solutions
Established Member.. Utkarsh_Mishra
Established Member..

Re: ITG_SETTINGS_ATTRIBUTES

Jump to solution

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)
2 Replies
Established Member.. Utkarsh_Mishra
Established Member..

Re: ITG_SETTINGS_ATTRIBUTES

Jump to solution

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 Acclaimed Contributor.
Acclaimed Contributor.

Re: ITG_SETTINGS_ATTRIBUTES

Jump to solution

Beautiful, thank you!

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.