Clarity

Expand all | Collapse all

Whitch slice request to use for getting the Unit value from Project BUDGET plan

Jump to Best Answer
  • 1.  Whitch slice request to use for getting the Unit value from Project BUDGET plan

    Broadcom Employee
    Posted 11-05-2015 10:35 AM

    Hi,

     

      I'm using Clarity R14.2, and I would like to know or understand witch Slice requests to use for extracting by Year (or month,or wathever) the Budget Plan in order to make a NSQL portlet query.

     

    Below a sample of a Budget plan associated with a project :


      I've try to look into the configuration (Administration > Data Administration > Time Slices), but I'vent find any request to respond to my need. bellow there are the tests done:

    (Reference documentation here)

     

    NamePRJ_BLB_SLICEREQUESTS.FIELDOutput TableResult
    Project::Detailed Budget Cost5000029 (not found in documentation)ODF_SL_BUDGET_COST

    Data populated, but not all budget plan are associated

    to a result, and when there is one, I can't find the relationship

    between the result and what I expect !

    Project::Detailed Budget Operating Cost5002011 (not found in documentation)ODF_SL_BUDGET_COST_OP

    Data populated, but does not correspond to

    a FIN_COST_PLAN_DETAILS.ID

    or FIN_PLANS.ID entry ?

    Project::Detailed Budget Actual Benefit5002012 (not found in documentation)ODF_SL_BUDGET_ACT_BENEmpty table
    Project::Detailed Budget Benefit5000026 (not found in documentation)ODF_SL_BUDGET_BENEFITEmpty table
    Project::Detailed Budget Captital Cost5002009 (not found in documentation)ODF_SL_BUDGET_COST_CAPEmpty table

     

    Extract of Technical doc regarding PRJ_BLB_SLICEREQUESTS.FIELD meaning :

     

    NameData TypeComment
    FIELDFIELD : NUMBER : NULLData column being sliced. Values are: ¦      0 = PRAssignment.prActCurve ¦      1 = PRAssignment.prEstCurve ¦      2 = Current assignment usage ¦      3 = PRJ_RESOURCES.prAvailCurve ¦      4 = PRTeam.prAllocCurve ¦      5 = PRTimeEntry.prActCurve ¦      6 = Current team usage ¦      7 = Current task usage ¦      8 = Current project usage ¦      9 = Current assignment cost ¦      10 = Current team cost ¦      11 = Current task cost ¦      12 = Current project cost ¦      13 = Non-current assignment usage ¦      14 = Non-current team usage ¦      15 = Non-current task usage ¦      16 = Non-current project usage ¦      17 = Non-current assignment cost ¦      18 = Non-current team cost ¦      19 = Non-current task cost ¦      20 = Non-current project cost ¦      21 = Tentative estimates

     

    Please not that I want to retreive the Unit amount not a cost.

     

    The following query has been use to test the result :

     

    SELECT

      I.ID,

      I.CODE,

      FP.NAME,

      FP.ID FP_ID,

      FPD.ID FPD_ID,

      FPD.TOTAL_UNITS,

      R.UNIQUE_NAME,

      SL.SLICE_DATE,

      SL.SLICE

    FROM

      INV_INVESTMENTS I

        INNER JOIN FIN_PLANS FP ON (FP.OBJECT_ID = I.ID AND FP.OBJECT_CODE =  I.ODF_OBJECT_CODE AND FP.IS_PLAN_OF_RECORD = 1 AND PLAN_TYPE_CODE = 'BUDGET')

          INNER JOIN FIN_COST_PLAN_DETAILS FPD on (FPD.PLAN_ID = FP.ID)

             INNER JOIN SRM_RESOURCES R ON (R.ID = FPD.ROLE_ID)

                --INNER JOIN ODF_SL_BUDGET_COST_M SL ON (SL.PRJ_OBJECT_ID = FPD.ID)

                 

    WHERE

      I.CODE = '<prjcode>'

     

    I've made several try by replacing the highlighted red table with some found n the PRJ_BLBSLICEREQUESTS table.

     

    So definitively I've surly done a mistake somewhere, any help, sample or documentation link will be appreciated !

    Thanks



  • 2.  Re: Whitch slice request to use for getting the Unit value from Project BUDGET plan

    Posted 11-05-2015 11:06 AM

    If you are looking at the planned data on the budget, you should look at -

    odf_ssl_cst_dtl_cost cst

    fin_cost_plan_details dtl

    fin_plans fin

     

    Join conditions

     

    cst.prj_object_id = dtl.id and

    dtl.plan_id = fin.id and

    fin.is_plan_of_record = 1 and

    fin.object_id = <project_id> and

    fin.plan_type_code = 'BUDGET

     

     

    For the budgeted benefit, I guess that would be populated if you have a benefit plan linked to your budget plan.

     

    Also, check these -

     

    Budget Financial Plans Details

    https://communities.ca.com/message/5199291#5199291

     

     

    Project - Financial Plans - Budget - Actual  field is mapped to which table

    https://communities.ca.com/message/241818181#241818181

     

     

     

     

    NJ



  • 3.  Re: Whitch slice request to use for getting the Unit value from Project BUDGET plan
    Best Answer

    Broadcom Employee
    Posted 11-05-2015 11:33 AM

    Hi navzjoshi00,

     

      You have point me in right direction.

     

      To adjust the answer, As I'm looking for unit of the budget plan, the table have a different name : odf_ssl_cst_dtl_units

     

      I've tested with the following query :

     

    SELECT

      I.ID,

      I.CODE,

      FP.NAME,

      FP.ID FP_ID,

      FPD.ID FPD_ID,

      FPD.TOTAL_UNITS,

      R.UNIQUE_NAME,

      SL.START_DATE,

      SL.SLICE *365

    FROM

      INV_INVESTMENTS I

        INNER JOIN FIN_PLANS FP ON (FP.OBJECT_ID = I.ID AND FP.OBJECT_CODE =  I.ODF_OBJECT_CODE AND FP.IS_PLAN_OF_RECORD = 1 AND PLAN_TYPE_CODE = 'BUDGET')

          INNER JOIN FIN_COST_PLAN_DETAILS FPD on (FPD.PLAN_ID = FP.ID)

            INNER JOIN SRM_RESOURCES R ON (R.ID = FPD.ROLE_ID)

                INNER JOIN odf_ssl_cst_dtl_units SL ON (SL.PRJ_OBJECT_ID = FPD.ID)          

    WHERE

      (1=1)

    AND I.CODE = '<Project Code>'

     

    Please note the "time 365" after SL.SLICE. This is due du the fact that we got Yearly Fiscal period, and the odf_ssl_cst_dtl_units.SLICE correspond to the number of unit dived by the number of day in the fiscal period.

     

    Problem solved !

     

    Thanks for the hint !



  • 4.  Re: Whitch slice request to use for getting the Unit value from Project BUDGET plan

    Posted 11-06-2015 03:08 AM

    You're welcome

     

    NJ