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)
Name | PRJ_BLB_SLICEREQUESTS.FIELD | Output Table | Result |
---|
Project::Detailed Budget Cost | 5000029 (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 Cost | 5002011 (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 Benefit | 5002012 (not found in documentation) | ODF_SL_BUDGET_ACT_BEN | Empty table |
Project::Detailed Budget Benefit | 5000026 (not found in documentation) | ODF_SL_BUDGET_BENEFIT | Empty table |
Project::Detailed Budget Captital Cost | 5002009 (not found in documentation) | ODF_SL_BUDGET_COST_CAP | Empty table |
Extract of Technical doc regarding PRJ_BLB_SLICEREQUESTS.FIELD meaning :
FIELD | FIELD : NUMBER : NULL | Data 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