Table fields and values calculations in Project ManagementIssue Understanding Date Calculations Planned Task date fields are populated by default with the following values: Planned Start Date = Parent Date (if any) Or TodayPlanned Duration = 1 Day These default values are calculated within the respective dictionary column in the planned_task table. It is not advisable to change these defaults, but rather override them if needed at each table extension under Dictionary Overrides. For Project and Project Tasks, the date calculations are based on the Schedule attached to the project record, according to the following criteria: - When the Actual Start and End Dates are both not available and empty => Planned End Date = Planned Start Date + Planned Duration - When the Actual Start Date is available and populated, but the Actual End/Actual Duration is empty => Planned End Date = Actual Start Date + Planned Duration In this case, the Planned End Date behaves like the Expected End Date based on the Actual Start Date. - When Actual Start Date and Actual End Date are both available and populated => Planned End Date = Planned Start Date + Planned Duration Actual Duration = Actual End Date – Actual Start Date. Allow Dates Outside the Schedule This column is in the Project Task table and enables the task calculation to fall within the 24-hour schedule instead of the schedule attached to the project. Parent Date Rollup The least Start Date and Most End Dates are rolled up to the parent records. Before Jakarta, the Duration was calculated based on the time gap between the earliest Start Date and the latest End Date. Since Jakarta and until London, the Duration is instead calculated based on the Original Date. These calculations are performed within the 'Recalculate' Business Rule. The calculations and rollup can be excluded at table level. All the Table Exclusions are set in the planned_task_recalculation_exclusions table. NOTE: It is not advisable to set either Project or Project Task in the Exclusions Table. Business Rules Name Table Description Recalculate Planned Task Recalculates the task and project hierarchy Recalculate – Delete Planned Task Recalculates the task and project hierarchy Calculate Start Date for Planned Task (Display) Planned Task Initializes the start date of the new task Initialize Node Planned Task Initializes Start Date, Duration based on the parent and set Wbs, Top Task, Top Program, Subtree root and so Set Close Data on Inactive Planned Tasks Sets the Actual End Date Sync duration and milestone on insert Planned Task Sync Duration and Milestone Sync duration and milestone on update Planned Task Sync Duration and Milestone Update Planned Date from Current Planned Task Sets the Original Dates Validate Empty Duration Planned Task Validates Duration Validate Start date for Child Task Planned Task Validates the Start date of a child task Validate Work End before Work Start Planned Task Validate if Actual End Date is before Actual Start Date Rollups and Exclusions The Planned Task offers the rollups of the child to the parent task. The Planned Rollup Architecture allows to roll up several data types to parent(s). The Rollup strategy varies for the types of selected fields. The default seeded rollups provided by the Planned Task are listed below: EffortRemaining HoursCost (Estimated and Actual)Benefit Rollup Each of the rollups has a seeded Business Rule in the Planned Task table to roll up the same. The generic rollup strategy sums up all the child values and rollups the aggregate sum to the parent record. This includes each of the four rollups listed above. By default, each column rolls up to the same column from the child to the parent record. The child task records are identified by their parent column values. Rollups can be Excluded or Configured. The Exclusion / Configurability comes with the two steps process described below. 1. Business Rules that trigger the Rollup. It is not advisable to disable it, as it disables the rollup in all the planned task extensions. 2. Rollup Configuration in the planned_task_rollup table: Column Description Parent Parent Table. Child Child Table. Field The field in the Child Table is to be aggregated. Navigator The field in the Child Table represents parent reference. Rollup Disables Rollup if set to false, otherwise true by default. If there are no entries found in the above table, the rollups are set as active by default. Business Rules Name Table Description Update Parent Effort Planned Task Effort Rollup Update Parent Effort – Delete Planned Task Effort Rollup Update parent actual effort Planned Task Actual Effort Rollup Update parent actual effort - delete Planned Task Actual Effort Rollup Set/Update Remaining Hours Planned Task Calculates the Remaining Hours Planned Task Estimated Cost Rollup Planned Task Estimated Cost Rollup Planned Task Planned Benefit Rollup Planned Task Planned Benefit Rollup Update Total Planned Cost Planned Task Planned Cost update Percent Complete Calculation The Percent Complete value is initially set as default to zero for every task. During the tasks workflow, the Percent Complete for an individual task is not automatically calculated and does not follow the actual work done. Users are in charge to enter and modify the Percent Complete values for each individual task, except the Parent task. The Percent Complete on the Parent Task is calculated as the sum of each child task’s Duration multiplied by its individual child-task Percent Complete value. When a task is closed or set to inactive, its Percent Complete value gets populated at 100%. Business Rules Name Table Description Update Parent Percent Complete Planned Task Percent Complete Rollup Update Parent Percent Complete – Delete Planned Task Percent Complete Rollup Set Close Data on Inactive Planned Task Set Percent Complete to 100% post closure State Management and Configurations The Planned Task inherits the state from the Task table. These states are categorized as follows and given the possible values listed within the brackets: PENDING_STATES [-5]OPEN_STATES [1]WORK_IN_PROGRESS_STATES [2]CLOSE_STATES [3;4;7] The default state values listed below have to be mentioned in the internal state handling APIs to default to a state according to the use case workflow. DEFAULT_PENDING_STATE = -5DEFAULT_OPEN_STATE = 1DEFAULT_WORK_STATE = 2DEFAULT_CLOSE_STATE = 3DEFAULT_SKIPPED_STATE = 7 State Rollup The state rollup is based on the child tasks if present: Parent State Child State Pending If all child records are pending Open If at least one child is Open and the rest are pending Working Progress If at least one child is WIP or Closed Complete Closed Complete If all the children are closed complete Business Rules Name Table Description Process State Change Project / Project Task State Change Handler Set State On Percentage Change Project Task Sets the state based on the percent change Validate state mapped to a bucket Project / Project Task Validates the state Time Constraints The time constraint of the task determines the following characteristics and task behavior: Start Date of the taskRelationship to be or not be applied between tasks There are two choices available for time constraints: As Soon As Possible (ASAP) Setting the time to constrain to ASAP makes the task inherit the start date for the parent task.When the Relationships are applied, the Start Date/End Date of the task adheres to the related tasks. Start on Specific Date (START_ON): Setting the time constraint to START_ON will make the task select a later date from the parent task.Relationships are not applied in this case. NOTE: There was an exception to convert the Start on Task to ASAP using the system property com.snc.project.allow_start_on_relations. This property is deprecated since Kingston. Original Dates The Original Dates (internal column names schedule_start_date and schedule_end_date) represent the initial planned dates of the project before it is moved to Work In Progress: Original Dates are always in sync with Planned Date until the Project is Pending/Open.Once the Project or at least one Task under it moves to Work In Progress, the Original Dates are frozen.The duration can be calculated from the Original Dates instead of the initial Planned Dates, but this part will be subject to redesign in future releases. For example, consider the project below in State = Pending: Task State Planned Start Planned End Original Start Original End Project 1 Pending 01-01-2018 08:00:00 01-02-2018 17:00:00 01-01-2018 08:00:00 01-01-2018 17:00:00 Task 1 Pending 01-01-2018 08:00:00 01-01-2018 17:00:00 01-01-2018 08:00:00 01-01-2018 17:00:00 Task 2 Pending 01-02-2018 08:00:00 01-02-2018 17:00:00 01-02-2018 08:00:00 01-02-2018 17:00:00 The project is moved to WIP and Task 1 is started a day later: Task State Planned Start Planned End Actual Start Original Start Original End Project 1 WIP 01-01-2018 08:00:00 01-03-2018 17:00:00 01-02-2018 08:00:00 01-01-2018 08:00:00 01-01-2018 17:00:00 Task 1 WIP 01-01-2018 08:00:00 01-02-2018 17:00:00 01-02-2018 08:00:00 01-01-2018 08:00:00 01-01-2018 17:00:00 Task 2 Pending 01-03-2018 08:00:00 01-03-2018 17:00:00 01-02-2018 08:00:00 01-02-2018 17:00:00 If the Project is already in WIP, Add Task gets added, and the Original Date of the Tasks are taken from the Planned Dates. If the Task’s Duration is updated, the Original End date gets updated too. This will roll up to the parent Project. Inter-Project Dependencies - Shadow Tasks and Shadow Relations When there is a relation set from one project to another, the following occurs: A copy of each of the tasks is created in the related other projects. This is called Shadow Tasks.A copy of the relation is created on the Shadow Tasks. This is called Shadow Relation.These tasks are determined by the orig_sys_id, linking to the actual task from which it got created.The shadow tasks are placed either above or below the related tasks, identified either by a predecessor or successor. This placement is done only on the initial creation.The Short Description and Actuals are always in sync with the actual tasks. After the creation of Shadow Tasks and Shadow Relations, the following occurs: In the predecessor Project, its successor is reset and recalculated to check if the relation can be applied.If the relationship is moving the task later than the actual task’s start date, a notification is generated.If the Inter Project Dependency Type of the relation was set to Hard while creating this relation, the notification is auto–accepted and applied on the successor Project.Else if the Inter Project Dependency type is Soft, it will show the notification on the successor project for approval. Relations: Relations can be created between two tasks in a parent-child hierarchy. The data model for the relationship is the following: Column Description Parent Predecessor / Parent of the Relation starting from Child Successor / Child of the Relation ending to Type Set to Predecessor Of or Successor Of Sub Type Type of Relation [FS, SS, FF, SF] Lag Lag / Lead in the Relation External If Inter Project Dependencies There are four different types of relations: Finish-To-Start: The related/successor task CANNOT Start Earlier than the Planned End of the predecessor task.Start-To-Start: The related/successor task CANNOT Start Earlier than the Planned Start of the predecessor task.Finish-To-Finish: The related/successor task CANNOT Finish later than the Planned End of the predecessor task.Start-To-Finish: The related/successor task CANNOT Start later than the Planned End of the predecessor task. If the relation is applied to the parent task, the child tasks inherit this relationship. The relation might not apply for the Tasks with START_ON Time Constraint, as described in the Time Constraints section above. Frequently Asked Questions Project dates are not getting updated: Validate the correctness of the Project using the Project Diagnosis Tool.Check if any of the tasks under the project is set to start before the start date of the Project. Parent states are not getting updated properly: Check the related Business Rules and scripts Included in case they have been customized. All the Project Tasks are getting updated with the same description: Check if the ORIG_SYS_ID column is created under the planned task.Review the Script Include ShadowTaskSync for possible customizations. Relations are not getting applied properly: Validate the correctness of the Project using the Project Diagnosis Tool.Check if there is any cyclic relation within the Project.