The logic in determining what value to use for a transaction reads from the bottom up in the project hierarchy:
If the Cost Type on the Task is set to a value (Capital or Operating), that value is used. If the Cost Type is blank, the logic looks to the next level up in the WBS.
If that level is, for instance, a summary task, and its Cost Type has a value, then that value is used for the sub-task with a blank Cost Type. If that level has a blank Cost Type, the logic looks to the next level up.
The logic will continue to search each level up for a value until it reaches the Project level. Since Cost Type is a required field at the Project level, there will always be a Cost Type value for every transaction.
So - if you have a simple project WBS structure with no indentures (no summary tasks or phases) on the project, and the task that you select for charging the non-labor to has no Cost Type value, and if the Project is set with a Cost Type of Capital, the non-labor charges will be recorded and reported as Capital.
Example:
| | | |
Project 1 | Cost Type: Capital | | |
| | | |
Level | WBS | Cost Type | |
Level 1 | Phase | Capital | From Template |
Level 2 | Work Package | Capital | From Template |
Level 3 | Task 1 | <blank> | Created By PM |
PM adds a timesheet Task 1 @ WBS level 3 under the Work Package a cost type value will only display if you select a value at the Level 3 for Task 1.
If Cost type is not defined for Task 1 it will inherit the Cost type from parent in the backend in this case from Work Package.
If level 2 does not have a cost type value Task 1 will inherit the Cost Type from Level 1 in the backend.
If none of the WBS levels have cost type defined Task 1 will inherit the value defined at Project level.
Query to get cost type information at database:
SELECT C.PRNAME, C.COST_TYPE FROM
( SELECT NVL(PT1.COST_TYPE, NVL(PT2.COST_TYPE, P.COST_TYPE)) AS COST_TYPE, PT1.PRID PRID, PT1.PRNAME,
ROW_NUMBER() OVER (ORDER BY PT2.PRWBSSEQUENCE DESC) ROW_NUM
FROM PAC_MNT_PROJECTS P, PRTASK PT1
LEFT OUTER JOIN PRTASK PT2 ON
PT2.COST_TYPE IS NOT NULL
AND PT2.PRPROJECTID = PT1.PRPROJECTID
AND PT2.PRWBSSEQUENCE <= PT1.PRWBSSEQUENCE
AND PT2.WBS_NNBSEQ > PT1.PRWBSSEQUENCE
AND PT2.PRID <> PT1.PRID
WHERE PT1.PRPROJECTID = 5004001 /* PASS PROJECT ID */
AND PT1.PRID = 5005006 /* PASS TASK PRID */
) C
WHERE C.ROW_NUM = 1
Function:
CREATE OR REPLACE FUNCTION NIKU.Z_TRANS_TASK_COST_TYPE
(P_PROJECT_ID INT, P_TASK_ID INT)
RETURN VARCHAR IS
V_COST_TYPE PRTASK.COST_TYPE%TYPE;
BEGIN
SELECT C.COST_TYPE INTO V_COST_TYPE
FROM ( SELECT NVL(PT1.COST_TYPE, NVL(PT2.COST_TYPE, P.COST_TYPE)) AS COST_TYPE, PT1.PRID PRID, PT1.PRNAME,
ROW_NUMBER() OVER (ORDER BY PT2.PRWBSSEQUENCE DESC) ROW_NUM
FROM PAC_MNT_PROJECTS P, PRTASK PT1
LEFT OUTER JOIN PRTASK PT2 ON
PT2.COST_TYPE IS NOT NULL
AND PT2.PRPROJECTID = PT1.PRPROJECTID
AND PT2.PRWBSSEQUENCE <= PT1.PRWBSSEQUENCE
AND PT2.WBS_NNBSEQ > PT1.PRWBSSEQUENCE
AND PT2.PRID <> PT1.PRID
WHERE PT1.PRPROJECTID = P_PROJECT_ID
AND PT1.PRID = P_TASK_ID
) C
WHERE C.ROW_NUM = 1;
RETURN TO_CHAR(V_COST_TYPE);
END;
GO
SELECT I.CODE PROJECT_CODE, I.NAME PROJECT_NAME, T.PREXTERNALID TASK_ID, T.PRNAME TASK_NAME, P.COST_TYPE PROJECT_COST_TYPE, T.COST_TYPE TASK_COST_TYPE,
Z_TRANS_TASK_COST_TYPE(I.ID, T.PRID) TRANS_COST_TYPE
FROM INV_INVESTMENTS I JOIN PRTASK T ON I.ID = T.PRPROJECTID AND I.CODE = '00000003'
JOIN PAC_MNT_PROJECTS P ON P.ID = I.ID
ORDER BY PRWBSSEQUENCE
Output: