Planning / Staffing baseline - Automatic creation

Hello,

We want to have a baseline of planing created automaticaly 1 time per month.

Is this possible ?

Parents
  • Hi Sébastien,

    Yes, this should be possible.

    In your project workflow, set up a timeout step that will fire every 24 hours. In this step, you can run a SQL Query that leverages the date from DB (SYSDATE) to check if you're on the right day of the month on which you want to capture your baselines.

    If the date a match, take your snapshots with special commands ksc_create_baseline (for the work plan) and ksc_create_baseline_staffing_profile (for the staffing profile).

    Since the workflow timeout is not extremely precise (it depends on the timeout reaper background service), you might set it to run every 23 hours and insert a row with current month in a custom table to indicate that your snapshots have been taken for this month - and check if a row already exists for that month before taking the snapshot, and skip if it already exists. If you need the snapshot to be taken at the same time of the day, just have your timeout fire more often, but don't set the value too low as it may create quite a lot of workflow-related data in your DB.

Reply
  • Hi Sébastien,

    Yes, this should be possible.

    In your project workflow, set up a timeout step that will fire every 24 hours. In this step, you can run a SQL Query that leverages the date from DB (SYSDATE) to check if you're on the right day of the month on which you want to capture your baselines.

    If the date a match, take your snapshots with special commands ksc_create_baseline (for the work plan) and ksc_create_baseline_staffing_profile (for the staffing profile).

    Since the workflow timeout is not extremely precise (it depends on the timeout reaper background service), you might set it to run every 23 hours and insert a row with current month in a custom table to indicate that your snapshots have been taken for this month - and check if a row already exists for that month before taking the snapshot, and skip if it already exists. If you need the snapshot to be taken at the same time of the day, just have your timeout fire more often, but don't set the value too low as it may create quite a lot of workflow-related data in your DB.

Children
  • Hi Etienne,

    I've tried the baseline creation for staffing and planning but I have an error with the one for the staffing profile.

    The step  ksc_create_baseline_staffing_profile have been added to the "Launch projet step" in the workflow without special parameters or anything. The following error is displayed.

    Running command: -project 46037 -status IN_FLIGHT -user 1234 Running command: com.kintana.core.server.execution.CreateBaseline -projectId 46037 -baselineName "Auto" -userID 1234-userName "xxxx" Running command: com.kintana.core.server.execution.CreateBaseLineStaffingProfile -staffingProfileId "[REQD.P.STAFF_PROF_ID]" -spBaselineName "[REQD.P.STAFF_PROF_NAME]" -userID "1234" -userName "xxx"

    For input string: "[REQD.P.STAFF_PROF_ID]" Invalid Options. Usage: java CreateBaselineStaffingProfile -staffingProfileId [REQD.P.STAFF_PROF_ID] -spBaselineName "[]" -userID [USER.USER_ID] -userName "[USER.USERNAME]"

  • Hi,

    Are you sure that the token [REQD.P.STAFF_PROF_ID] is valid? Could you share a screenshot of all the Request Header fields [REQ.P.*] and Request Details fields [REQD.P.*] in your project request type? They should also be listed in the Token Builder available in the "Edit Command" popup of the request type screen.

    I don't think that you can find the staffing profile ID in a Request Details field [REQD.P.STAFF_PROF_ID] by default - you may need to use another token to pass the staffing profile ID to the special command.

  • Hi,

    These are the defaults token proposed by the workbench.

    Even if we launch only the command "ksc_create_baseline_staffing_profile" without parameters, the error is the same.

    The token [REQD.P.STAFF_PROF_ID] is not present in the token builder.

  • Hi,

    - Default Tokens proposed by the workbench are just hard coded and shouldn't be considered as always working by default.

    - You can't look at the Staffing Profile tokens in the token builders - these are the tokens that are avaialble when you are in the "context" of a Staffing Profile, i.e. on the staffing profile page. Here you're in a project, or more exactly a Project Details (which is a request), so you have to use the Request Details & Request Header tokens.

    - Most importantly, I don't see where the Staffing Profile field is in the list of request fields that you sent me. You need to have the Staffing profile somewhere as a field if you want to refer to it directly as a [REQ] token. If you do NOT have project Staffing Profile included as a field in your request details, then you'll have to retrieve the Staffing Profile ID using a SQL Query (using the Request ID or Project ID and looking for the corresponding Staffing Profile ID in table KCRT_FG_PFM_PROJECT) and the store that SQL result in a variable, and use that variable in your special command when you need to pass the staffing profile ID.

  • Hi,

    We want to create a baseline of the staffing profile and planning when we go to the next step of the project.

    This is working for the planning but not for the staffing.

    How can we do this without these tokens ?

  • Hi Sebastien,

    I already gave you some hints in my previous post:

    If you do NOT have project Staffing Profile included as a field in your request details, then you'll have to retrieve the Staffing Profile ID using a SQL Query (using the Request ID or Project ID and looking for the corresponding Staffing Profile ID in table KCRT_FG_PFM_PROJECT) and the store that SQL result in a variable, and use that variable in your special command when you need to pass the staffing profile ID.

    Let me provide more details here.

    I can see from your latest screenshot that you already have the project ID available in token [REQ.P.KNTA_PROJECT_PLAN]. I also assume that you should also have the Project Number (i.e. the request ID) in token [REQ.P.REQUEST_ID]. 
    So we need to find a SQL Query that can return the Staffing Profile ID based on either the Project ID or the Project Number. I can think of two ways:

    1) In table KCRT_FG_PFM_PROJECTS you have columns REQUEST_ID (i.e. Project Number), PRJ_PROJECT_ID, and PRJ_STAFF_PROF_ID. Very easy!

    2) If you feel that option 1) is too simple, you can check in table RSC_STAFFING_PROFILES for the value of column STAFFING_PROFILE_ID where CONTAINER_ENTITY_TYPE_CODE = 1 (that means "project") and CONTAINER_ENTITY_ID is your Project ID.

    I like to keep it simple, so I'll use option 1) :) Here's the special commands steps that you can use for running ksc_create_baseline_staffing_profile:

    ksc_itg_run_sql QUERY_STRING="select PRJ_STAFF_PROF_ID FROM KCRT_FG_PFM_PROJECT WHERE prj_project_id = [REQ.P.KNTA_PROJECT_PLAN]"
    ksc_set P_SP_ID=[SQL_OUTPUT]
    ksc_create_baseline_staffing_profile -staffingProfileId "[P_SP_ID]" -spBaselineName "Auto SP baseline on project creation"

    Note that I hard-coded a name for the baseline, feel fee to change it - but don't use the default proposed token [REQD.P.STAFF_PROF_NAME] as this is not a valid token. You can pick the project name instead (might be in [REQ.VP.KNTA_PROJECT_PLAN]), or use a better hard-coded value. Also, I don't think there's any reason to pass the userId or username to the special command.

    Let me know if this works for you now.

  • Hi,

    Thank you for all these informations.

    I've adapted the workflow but it's not working because the commands aren't used.

    Running command: -project 46804 -status IN_FLIGHT -user 36039 Running command: com.kintana.core.server.execution.CreateBaseline -projectId 46804 -baselineName "Auto" -userID 12345 -userName "xxx" Running command: com.kintana.core.server.execution.CreateBaseLineStaffingProfile -staffingProfileId "[REQD.P.STAFF_PROF_ID]" -spBaselineName "[REQD.P.STAFF_PROF_NAME]" -userID "12345" -userName "xxxx" For input string: "[REQD.P.STAFF_PROF_ID]" Invalid Options. Usage: java CreateBaselineStaffingProfile -staffingProfileId [REQD.P.STAFF_PROF_ID] -spBaselineName "[]" -userID [USER.USER_ID] -userName "[USER.USERNAME]"

  • Hi,

    The logs say:

    -staffingProfileId "[REQD.P.STAFF_PROF_ID]" -spBaselineName "[REQD.P.STAFF_PROF_NAME]"

    That doesn't match the updated commands you're sharing in the screenshot. Are you sure you saved your workflow before trying again? You may also need to "move away" in your workflow to ensure that you're picking the modified workflow step and not a copy of the old one. Just navigate a bit away from the current step in your workflow and go back to it, or, if that still doesn't work, try to reproduce the issue with a new project.

  • Hi,

    I've  moved the ksc_create_baseline_staffing step first and created the command with the special command builder, the command is now applied but an error is still present.

    Running command: com.kintana.core.server.execution.CreateBaseLineStaffingProfile -staffingProfileId "[P_SP_ID]" -spBaselineName "xxxx_auto_10-29-2021_staff" -userID "12345" -userName "xxx"
    For input string: "[P_SP_ID]"
    
    
    Invalid Options.
    
    Usage: java CreateBaselineStaffingProfile -staffingProfileId [REQD.P.STAFF_PROF_ID] -spBaselineName "[]" -userID [USER.USER_ID] -userName "[USER.USERNAME]"
  • The token P_SP_ID doesn't resolve. The most likely reason is that the SQLquery don't return any value - could you run it in the Admin Console's SQL Runner (replace the token with the Project ID value) and share the result?

    Also, I have a stupid question, but did you check whether your project actually has a staffing profile created? Unlike financial summaries, Staffing Profiles are not automatically created upon project creation, so you can't take a SP Baseline if there's no SP.