Queries for Spread Sizing

Previous TopicNext TopicContents

Activity Spread Estimate Based on Median Activity Length

select

median(

greatest(nvl(target_end_date,to_date('12122000','mmddyyyy'))

,nvl(act_end_date,to_date('12122000','mmddyyyy'))

,nvl(reend_date,to_date('12122000','mmddyyyy'))

,nvl(rem_late_end_date,to_date('12122000','mmddyyyy')) )

-

least(nvl(target_start_date,to_date('12122199','mmddyyyy'))

,nvl(act_start_date,to_date('12122199','mmddyyyy'))

,nvl(restart_date,to_date('12122199','mmddyyyy'))

,nvl(rem_late_start_date,to_date('12122199','mmddyyyy')) )

) * count(*) Spread_Rows

from task t inner join project p on p.proj_id = t.proj_id and orig_proj_id is null

where task_type in ('TT_Task','TT_Rsrc')

Resource Assignment Spread Estimate Based on Median Activity Length

select

median(

greatest(nvl(tr.target_end_date,to_date('12122000','mmddyyyy'))

,nvl(tr.act_end_date,to_date('12122000','mmddyyyy'))

,nvl(tr.reend_date,to_date('12122000','mmddyyyy'))

,nvl(tr.rem_late_end_date,to_date('12122000','mmddyyyy')) )

-

least(nvl(tr.target_start_date,to_date('12122199','mmddyyyy'))

,nvl(tr.act_start_date,to_date('12122199','mmddyyyy'))

,nvl(tr.restart_date,to_date('12122199','mmddyyyy'))

,nvl(tr.rem_late_start_date,to_date('12122199','mmddyyyy')) )

) * count(*) Spread_Rows

from taskrsrc tr inner join project p on p.proj_id = tr.proj_id and orig_proj_id is null

inner join task t on t.task_id = tr.task_id

where task_type in ('TT_Task','TT_Rsrc')

Oracle ODS Database

The Oracle ODS database is an optional target database for operational-level reporting. It creates the views of the P6 EPPM base tables and their corresponding spread data tables. The space usage for the Oracle ODS Database can be derived from a combination of the size of the PMDB database and the size of the spread data. The Oracle ODS Database has the following types of table data:

Indexing in the Oracle ODS Database defaults to the same indexing as the P6 EPPM Database. This should be augmented and adjusted based on site-specific reporting needs.



Legal Notices
Copyright © 1999, 2014, Oracle and/or its affiliates. All rights reserved.

Last Published Friday, January 31, 2014